Get last date in measure, with filters

[hello-world]

Hi everyone,

How can I create a measure which gets the last/max date from another measure which is returning a max date with that has been filter, while plotting against a date axis?

My problem is: I want to plot one cumulative value for one specific date, which is determined as the last date using filters in the calculate statement. When I use MAXX, it still gives me back all the values since my x-axis uses dates.

[v-yalanwu-msft · Accepted solution]

Hi @hello-world  , 

According to your description, you could create two measures, One is used as cumulative value for maximum date,

and another is used to influence the x-axis. the following formula to create :

Step1: create a sum measure (The  [Measure]  here is that you mentioned “ from another measure which is returning a max date“)

sum maxdate =

 SUMX(FILTER(ALL('table'),[Product]=MAX('table'[Product])&&[Date]<=[Measure]),[Sale Price])

Step2:  Create a flag measure

flag = IF(MAX([Date])=[Measure],1,0)

Step3: Apply the flag measure into filter and the final output is shown below

The final output is shown below:

and  

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@hello-world , Not very clear. Can you explain

measure =
var _max = maxx(allselected('Date1'), 'Date1'[Date])

return
CALCULATE(SUM(Sales[Sales Amount]),filter(allselected('date'),'date'[date] <=max('date'[date]) && 'Date'[date] <= _max))