Latest Result Based on Date Slicer Selection

[user919]

Below outlines an example of what we are trying to accomplish with the DAX calculation.

For the purposes of this example, I have listed one manager, four different Transaction Dates, 2 Feature IDs, and Attorney Rep Count (the metric we are trying to roll-up). For Attorney Rep Count, Attorney Rep represents a “Yes” or “No” response and can only be a 1 or a 0.

Mngr  Trns_Date   Feature_IDs  Atty_Rep_Cnt
ABC    1/1/2022       1234                   0
ABC    1/2/2022       5678                   1
ABC    3/15/2022     1234                   1
ABC    3/16/2022     5678                   1

My goal is to display the latest metric for the time period selected for each Feature ID and then roll it up at a categorical level (in this example, manager represents the categorical level). As displayed above, each Feature Key could be have several rows of data with multiple transaction dates.

For this example, if the end user selects 1/1/2022 through today in the slicer, Atty_Rep_Cnt for Manager “ABC” it should equal 2, not 3. 

[v-yadongf-msft]

Hi @user919 ,

Has your problem been solved? If solved, please consider Accept it as the solution to help the other members find it more quickly.

Best regards,

Yadong Fang

[v-yadongf-msft · Accepted solution]

Hi @user919 ,

Please try following DAX:

Latest Result = IF(
    'Table'[Trns_Date] = 
    MAXX(FILTER('Table','Table'[Feature_IDs] = EARLIER('Table'[Feature_IDs])),
    'Table'[Trns_Date]),
    'Table'[Atty_Rep_Cnt]
    )

You will get result fort Atty_Rep_Cnt for Manager “ABC” is 2, not 3. 

Best regards,

Yadong Fang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@user919 , Try like

Last Qty = Var _max = maxx(filter( ALLSELECTED(Data1), Data1[Mngr] = max(Data1[Mngr]) && [Feature_IDs]= max(Data1[Feature_IDs]) ),Data1[Trns_Date])
return
CALCULATE(sum(Data1[Atty_Rep_Cnt]), filter( (Data1), Data1[Mngr] = max(Data1[Mngr]) && [Feature_IDs]= max(Data1[Feature_IDs]) && Data1[Trns_Date] =_max))

Sum Last Qty = sumx(Summarize(Data1,Data1[Mngr],data1[Feature_IDs] ) , [Last Qty])

refer

https://amitchandak.medium.com/power-bi-get-the-sum-of-the-last-latest-value-of-a-category-f1c839ee8...