Using aggregation in if clause

[Anton1]

Hello,

I have a table with columns:

Date, Sales

I need to create a chart with Month as dimension, and Sum of sales for last sale day of the month in data as measure.

The last sales day of the month does not always equal to last day of the month, there might be dates with no sales.

For example:

Date DD.MM.YYYY	Sales
01.01.2019	100
01.01.2019	500
05.01.2019	600
25.01.2019	300
25.01.2019	400

And I should get:

Month	Sum of last sale date sales
Jan 2019	700

In February last sale date may be any date, and so on.

I need to calculate it in chart.

Thank you!

[dplr-rn]

easiest way would be to identify the last sales day per month while loading the data and introduce  a flag.

so in your example the rows for 25.01.2019  will have the flag there.

in the chart then you can use set analysis with flag.

e.g. Month year as dimension and Sum({<LastSalesDay={'Y'}>}Sales)