Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
Please help to add column to count the repetation of values by grouping the Date Columns.
Final ID | SLA Result | Report Date | Count |
12 | Not Met | 01-10-2021 | 1 |
12 | Not Met | 01-11-2021 | 2 |
12 | Not Met | 01-12-2021 | 3 |
12 | Not Met | 01-07-2022 | 1 |
12 | Not Met | 01-08-2022 | 2 |
12 | Not Met | 01-10-2022 | 1 |
12 | Not Met | 01-11-2022 | 2 |
Here, Group dates by consecutively, If any blank/stop on the dates, again the group count it from 1,2,3
abouve data, Final 12, and not met contineously appearing Oct 2021 to Dec 2021, then values appearing as 1,2 ,3
after no data mis from Jan to Jun, It starts from Jul 22 to Nov 2022. Then the count should starts again form 1,2 ,3,4
Please help
Solved! Go to Solution.
Hi, @ssk_1984
You can try the following methods.
Column:
Previous Date = MAXX(FILTER(ALL('Table'),[Report Date]<EARLIER('Table'[Report Date])),[Report Date])
Diff = DATEDIFF([Previous Date],[Report Date],MONTH)
First day = IF([Diff]<>1,1,BLANK())
Consecutive days =
VAR _lastdate =
CALCULATE ( MAX ('Table'[Report Date]),
FILTER ('Table',
[Report Date] <= EARLIER ( 'Table'[Report Date])
&& [First day] = 1 ) )
Return
CALCULATE (
COUNT('Table'[Report Date]),
FILTER ('Table',[Report Date]>= _lastdate
&& [Report Date] <= EARLIER ( 'Table'[Report Date]) ) )
Is this the result you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @ssk_1984
You can try the following methods.
Column:
Previous Date = MAXX(FILTER(ALL('Table'),[Report Date]<EARLIER('Table'[Report Date])),[Report Date])
Diff = DATEDIFF([Previous Date],[Report Date],MONTH)
First day = IF([Diff]<>1,1,BLANK())
Consecutive days =
VAR _lastdate =
CALCULATE ( MAX ('Table'[Report Date]),
FILTER ('Table',
[Report Date] <= EARLIER ( 'Table'[Report Date])
&& [First day] = 1 ) )
Return
CALCULATE (
COUNT('Table'[Report Date]),
FILTER ('Table',[Report Date]>= _lastdate
&& [Report Date] <= EARLIER ( 'Table'[Report Date]) ) )
Is this the result you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@ssk_1984 , refer Continuous streak
Continuous streak : https://youtu.be/GdMcwvdwr0o
refer
Continuous Streak -https://community.powerbi.com/t5/Desktop/Need-help-in-DAX/m-p/1277302#M559393
https://community.powerbi.com/t5/Quick-Measures-Gallery/Power-BI-Continuous-Streak-One-Day-Differenc...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Continuous-Streak-With-One-Day-Break/ba-p/1...
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
42 | |
33 | |
30 | |
18 | |
18 |