Count rows filtered by RANK for previous month

David_Morris · ‎10-03-2023

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/month, but not for the PRIOR month.

Measure _CountDistinctCurrentMonth seems to be working.

Measure _CountDistinctPreviousMonth is not working. It returns ZERO for everything.

My record set has an entry for every day of every month for every ID. I need to get the most recent entry for each ID depending on the month selected in the year/month slicer. If I choose september, it should return the record dated 30th September. If choose October, and today is the 4th October, then it should return the record dated 4th October.

To ensure I get the most recent record per ID I created a measure called __Rank. This appears to work correctly from what I can see, and flags the most recent record with a "1" and will change depending on the year/month I select from the year/month slicer.

__Rank = 
RANKX (
    FILTER (
        ALL ( 'Work Items' ),
        'Work Items'[Work Item Id] = MAX ( 'Work Items'[Work Item Id] )
            && 'Work Items'[__YearMonth] = MAX ( 'Work Items'[__YearMonth] )
    ),
    CALCULATE ( MAX ( ( 'Work Items'[Date] ) ) ),
    ,
    DESC
)

The measure _CountDistinctCurrentMonth seems to work, giving me the correct result based on what year/month I choose from the year/month slicer on the page.

_CountDistinctCurrentMonth = 
CALCULATE(
    COUNTROWS( DISTINCT('Work Items'[Work Item Id]) ),
    FILTER('Work Items',[__Rank] = 1)
)+ 0

The measure _CountDistinctPreviousMonth does not work and it is this measure that I am wanting help with.

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

I guess the way I have the filtering logic is cancelling itself out and not returning anything at all.

If I the following coun of records:

August = 5 records

September = 10 records

October = 15 records

Then If I choose October in the year/month slicer, then I want CurrentMonth = 15 and PreviousMonth = 10.

Then If I choose September in the year/month slicer, then I want CurrentMonth = 10 and PreviousMonth = 5.

Can you please help provide advice on correcting the measure _CountDistinctPreviousMonth to filter by __Rank = 1 AND only do so for records for the previous month.

isjoycewang · ‎10-04-2023

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 · ‎10-04-2023

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.