Forum Discussion

anin's avatar
anin
Frequent Visitor
5 years ago
Solved

Count specific values based on latest date

Hello,   I would like to count data entries with a specific value based on latest selected less than or equal date and have created a date table with a separate slider in my report.   This is my ...
Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    5 years ago

    Hi  anin ,

     

    Modify the measure as below:

     

    Measure = 
var _maxdate=CALCULATE(MAX('Table'[Date]),FILTER(ALLSELECTED('Table'),'Table'[Date]<=SELECTEDVALUE('calendar table'[Date])&&'Table'[ID]=MAX('Table'[ID])))
var _tab=SUMMARIZE('Table','Table'[ID],'Table'[Date],"value",IF(MAX('Table'[Date])=_maxdate&&MAX('Table'[Value1])=2&&MAX('Table'[Value2])=2,1,BLANK()))
Return
SUMX(_tab,[value])
    sum = SUMX(VALUES('Table'[ID]),[Measure])

     

    And you will see：

    For the related .pbix file,pls see attached.

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my post as a solution!

v-kelly-msft's avatar
v-kelly-msft
Community Support
5 years ago

Hi anin ，

 

First create a calendar table;

Then create a measure as below:

Measure = 
var _maxdate=CALCULATE(MAX('Table'[Date]),FILTER(ALL('Table'),'Table'[Date]<=SELECTEDVALUE('calendar table'[Date])&&'Table'[ID]=MAX('Table'[ID])))
Return
CALCULATE(DISTINCTCOUNT('Table'[ID]),FILTER(ALL('Table'),'Table'[Value1]=2&&'Table'[Value2]=2&&'Table'[Date]=_maxdate))+0

And you will see:

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

anin's avatar
anin
Frequent Visitor
5 years ago

Hello v-kelly-msft ,

Thank you for your response and your help. It is very much appreciated!
I am getting closer, but not yet getting exactly the results I want.

The filter should not be set to exactly the defined _maxdate.
I want the count of all ID's where the entry is the closest equal/less than the defined _maxdate and Value1&2 = 2

 

Perhaps this dataset and desired outcome explains it better:

 

 

If the selected day is 5th of february the highlighted entries are evaluated and the count is 2

 

If the selected day is 8th of february the highlighted entries are evaluated and the count is 1

 

Also I would like any other selected filters in the report page to remain selected for the card.

 

Here is a link to my dataset:

https://file.io/nYAxr2IMLCcU 

 

Once again thank you for your help!

 

Kind regards

  • v-kelly-msft's avatar
    v-kelly-msft
    Community Support
    5 years ago

    Hi  anin ,

     

    Modify the measure as below:

     

    Measure = 
var _maxdate=CALCULATE(MAX('Table'[Date]),FILTER(ALLSELECTED('Table'),'Table'[Date]<=SELECTEDVALUE('calendar table'[Date])&&'Table'[ID]=MAX('Table'[ID])))
var _tab=SUMMARIZE('Table','Table'[ID],'Table'[Date],"value",IF(MAX('Table'[Date])=_maxdate&&MAX('Table'[Value1])=2&&MAX('Table'[Value2])=2,1,BLANK()))
Return
SUMX(_tab,[value])
    sum = SUMX(VALUES('Table'[ID]),[Measure])

     

    And you will see：

    For the related .pbix file,pls see attached.

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my post as a solution!