March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Register NowGet certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
I am trying to create a columnd to show me prior month status but because my months have gaps in them I am getting blanks
I have the following data. The first 3 are in my dataset and I am trying to create the 4th
Report Month | Customer | Status | Prior Month Status |
Jan | ABC | Red | |
Feb | ABC | Red | Red |
March | ABC | Yellow | Red |
Apr | ABC | Yellow | Yellow |
May | ABC | Green | Yellow |
Jan | DEF | Red | |
Feb | DEF | Yellow | Red |
May | DEF | Green | Yellow |
Jan | GHI | Red | |
March | GHI | Yellow | Red |
May | GHI | Green | Yellow |
Solved! Go to Solution.
Please try
Prior Month Status =
MAXX (
TOPN (
1,
FILTER (
CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[Customer] ) ),
'Table'[Month] < EARLIER ( 'Table'[Month] )
),
'Table'[Month]
),
'Table'[Status]
)
it's date type
Please try
Prior Month Status =
MAXX (
TOPN (
1,
FILTER (
CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[Customer] ) ),
'Table'[Month] < EARLIER ( 'Table'[Month] )
),
'Table'[Month]
),
'Table'[Status]
)
omg this worked THANK YOU
User | Count |
---|---|
31 | |
19 | |
15 | |
14 | |
10 |