Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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.
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
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.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
86 | |
85 | |
84 | |
67 | |
49 |
User | Count |
---|---|
131 | |
111 | |
97 | |
71 | |
67 |