Forum Discussion

David_Morris's avatar
David_Morris
Frequent Visitor
2 years ago

Count rows filtered by RANK for previous month

I am having difficulty with creating a measure to count records filtered by the most recent record per the PRIOR year/month chosen by a date slicer. I seem to have it working for the CURRENTyear/mont...
Need Help
isjoycewang's avatar
isjoycewang
Solution Supplier
2 years ago

Hi David_Morris ,

 

Please try below measure, thanks.

_CountDistinctPreviousMonth = 
CALCULATE(
    COUNTROWS( DISTINCT('Work Items'[Work Item Id]) ),
    FILTER(ALL('Work Items'),[__Rank] = 1),
    PREVIOUSMONTH('Date'[Date])
)+ 0

 

 

David_Morris's avatar
David_Morris
Frequent Visitor
2 years ago

Thank you for your suggestion. Unfortunately that presented an error on the table visual.

 

Your suggestion got me thinking, and I may have resolved it though. I decided to get rid of the RANKXX field, and created a field to flag if the last date of each month, or if last record altogether.

__IsLastTransactionDateOfMonth = 
// If the date of the record is the last date of the month (such as 30th Sept)
// OR
// If the date is the latest record alltogether (such as the 15th with no other records after the 15th)
// Then return 1, else return 0

var _EOM = EOMONTH('Work Items'[Date],0)

return if( max('Work Items'[Date]) = 'Work Items'[Date] || 'Work Items'[Date] = _EOM, 1, 0)

 

Then I changed my two measures to the below.

_CountDistinct = 
CALCULATE(
    COUNTROWS( DISTINCT('Work Items'[Work Item Id]) ),
    'Work Items'[__IsLastTransactionDateOfMonth] = 1
)+ 0

_CountDistinctPreviousMonth = 
CALCULATE(
    COUNTROWS( DISTINCT('Work Items'[Work Item Id]) ),
    'Work Items'[__IsLastTransactionDateOfMonth] = 1,
    PREVIOUSMONTH('Date'[Date])
)+ 0

 

Now it seems to be working.