Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Count Latest Date

Hi, I have another Dax Measure problem.

 

I need to create measure, which counts Manager Status in the latest date for each manager ID, additionaly date slicer available on the page should impact that measure.

 

My data source :

 

is like that:

 

DateManagerIDStatus
18.08.20201IRE
18.08.20202GER
18.08.20205ASI
18.08.20203 ENG
31.09.20191IRE
31.09.20194SWI
31.09.20192GER
12.08.20151IRE
12.08.20152GER
11.09.20183ENG

 

Now i want to count how many IRE/ GER / ENG/ ASI/SWI Managers we do have in the latest date for each manager ID- so from above example how it looks for the latest date for each manager ID. So the table would have staus on axis then count of how many managers fit into each so based on the data above you would have 1 of each of IRE/GER/ENG/ASI/SWI as even though the latest date may not be 18.08.2020 for SWI it would still be included as for that manager ID thats the latest status for him.

 

 

What's more Date filter which is applied as a slicer on the report page should filter that date?

 

Could you please help me ?

I tried with something like that : 

Measure = CALCULATE(COUNT('Manager Ratings'[Status]),MAX('Table'[Date]) , but MAX does not work in this case.
 
Thanks in advance!
Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi,

     

    Please check the measure.

    Measure = CALCULATE(COUNT('Table'[ManagerID]),FILTER(ALLEXCEPT('Table','Table'[Status]),'Table'[Date]=MAXX(ALL('Table'),'Table'[Date])))

    Result would be shown as below.

     

     

    Best Regards,

    Jay

4 Replies

  • Tahreem24's avatar
    Tahreem24
    Super User
    5 years ago

    Try this out:

    Measure = CALCULATE(COUNT('Manager Ratings'[Status]),FILTER(Table, Table[Date]=MAX('Table'[Date]))) 

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Tahreem24thank you for your reply, this is what i have tried although this seems to just look at the latest date by status. Whereas i would need to look at this per Manager ID? so i could create a bar chart to say satus as the axis and the count to be based on the latest date per manager ID how many fit into each status. Does that make sense?

      • VahidDM's avatar
        VahidDM
        Super User
        5 years ago

        Hi,

         

        First, September does not have day 31!

        second, your question is not totally clear for me, but please check this measure:

        measure = 
VAR _MaxDate =
    CALCULATE ( MAX ( 'Table'[Date] ), FILTER ( 'Table', 'Table'[Manager ID] ) )
RETURN
    CALCULATE ( COUNT ( 'Table'[Date] ), 'Table'[Date] = _MaxDate )

        Result would be shown as below:

        Don't forget to give thumbs up and accept this as a solution if it helped you!!!

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi,

     

    Please check the measure.

    Measure = CALCULATE(COUNT('Table'[ManagerID]),FILTER(ALLEXCEPT('Table','Table'[Status]),'Table'[Date]=MAXX(ALL('Table'),'Table'[Date])))

    Result would be shown as below.

     

     

    Best Regards,

    Jay