Show data on last date of the month

[tracycrown]

Dear all

Kindly advise how to plot cumulative sales data only on last date of the month ?.

Thank you

Tracy

[tresesco]

Try:        FirstSortedValue([Cum Sales],-Date)

[Chanty4u]

may be

= FirstSortedValue([Cum Sales],Date)

[Gysbert_Wassenaar]

Create a new flag field in the script:

LOAD

     Date,

     if(Date = MonthEnd(Date), 1, 0) as IsLastDayOfMonth,

     [Cum Sales]

FROM

     ...

     ;

You can then use that new flag field in your expression:

sum([Cum Sales]) changes to sum({<IsLastDayOfMonth={1}>}[Cum Sales])

[tracycrown]

Dear gysbert wassenaar

I tried your method but no data shown on the chart, please check and advise.

Thank You

Tracy

[tracycrown]

Dear tresesco

Please advise what is the meaning of FirstSortedValue ?

Thank You

Tracy

[Gysbert_Wassenaar · Accepted solution]

Ah, yes. Silly me. MonthEnd returns a value that also has a time part. Try this instead:

if(Date = Floor(MonthEnd(Date)), 1, 0) as IsLastDayOfMonth