Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hello)
I have a problem could you please help me to count consecutive Months from the last available month in the data set for Users (column ID)
For example, for User 1 I want to have count 3 (3 months when a user with id 1 has values)
User 2 - the same 3
User 3 - 0 (because there is no values)
User 4 - 3 and so on
Maybe there are any chances to solve it?
Thank you so much for support.
Solved! Go to Solution.
Hi @yakovlol ,
Firstly, please make sure your table looks like as below.
Or you can try UNPIVOT function to translate it in Power Query Editor.
Measure:
Measure =
VAR _STEP1 =
ADDCOLUMNS (
'Table',
"Flag",
IF (
EOMONTH ( 'Table'[Date], 0 )
+ 1
IN CALCULATETABLE ( VALUES ( 'Table'[Date] ), ALLEXCEPT ( 'Table', 'Table'[ID] ) ),
1,
0
),
"MaxDate",
MAXX (
FILTER ( 'Table', 'Table'[ID] = EARLIER ( 'Table'[ID] ) ),
'Table'[Date]
)
)
VAR _STEP2 =
ADDCOLUMNS (
_STEP1,
"PrevioiusDate",
MAXX (
FILTER ( _STEP1, [ID] = EARLIER ( [ID] ) && [Date] < [MaxDate] && [Flag] = 0 ),
[Date]
)
)
RETURN
COUNTAX (
FILTER (
_STEP2,
[Date] > [PrevioiusDate]
&& [Date] <= [MaxDate]
&& [Value] <> 0
),
[ID]
)
Result is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @yakovlol ,
Firstly, please make sure your table looks like as below.
Or you can try UNPIVOT function to translate it in Power Query Editor.
Measure:
Measure =
VAR _STEP1 =
ADDCOLUMNS (
'Table',
"Flag",
IF (
EOMONTH ( 'Table'[Date], 0 )
+ 1
IN CALCULATETABLE ( VALUES ( 'Table'[Date] ), ALLEXCEPT ( 'Table', 'Table'[ID] ) ),
1,
0
),
"MaxDate",
MAXX (
FILTER ( 'Table', 'Table'[ID] = EARLIER ( 'Table'[ID] ) ),
'Table'[Date]
)
)
VAR _STEP2 =
ADDCOLUMNS (
_STEP1,
"PrevioiusDate",
MAXX (
FILTER ( _STEP1, [ID] = EARLIER ( [ID] ) && [Date] < [MaxDate] && [Flag] = 0 ),
[Date]
)
)
RETURN
COUNTAX (
FILTER (
_STEP2,
[Date] > [PrevioiusDate]
&& [Date] <= [MaxDate]
&& [Value] <> 0
),
[ID]
)
Result is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
User | Count |
---|---|
118 | |
66 | |
65 | |
56 | |
50 |
User | Count |
---|---|
182 | |
85 | |
67 | |
61 | |
53 |