Forum Discussion
Dax consecutive Count by months
Hello all,
i'm sorry if i posted this in a wrong category (i'm new in this community)
I would like to ask for a help.
I'm trying to count consecutive months.
The logic is:
if the Latency Value > Target for 2 and more months in a row then write "Watch out"
I've got a problem also when the year turns into new one.
For example if target was not reached 3 months in a row (Nov 2021, Dec 2021, Jan 2022) i am getting only consecutive for year 2021 of "0" and "1" but the 3rd number is (-10) instead of "2". - the right consecutive should be: target not reached: Nov 2021 (0) , Dec 2021 (1) , Jan 2022 (2)
Below is how i would like the report to be shown
Thank you for every help
Hi Anonymous ,
Please create a date table and the relationship.
Then create these measures:
Measure = IF ( SUM ( 'Table'[Latency Value] ) > SUM ( 'Table'[Target value] ), 1, 0 )Measure2 = VAR tab = SUMMARIZE ( ALLSELECTED ( 'Date' ), 'Date'[Year_Month], "M", [Measure] ) VAR mindate = CALCULATE ( MAX ( 'Date'[Year_Month] ), ALLSELECTED ( 'Date' ), FILTER ( tab, 'Date'[Year_Month] < MAX ( 'Date'[Year_Month] ) && [M] = 0 ) ) RETURN IF ( [Measure] = 1, SUMX ( FILTER ( tab, 'Date'[Year_Month] <= MAX ( 'Date'[Year_Month] ) && 'Date'[Year_Month] > mindate ), [M] ) - 1 )If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
10 Replies
Your sample data are missing rows for Feb and Apr. Is your logic based on 2+ consecutive values or actually calculate the # of months difference?
Pat
Hello Pat,
data are only on monthly basis.
I have multiple files with data, in the filename there is month and year (which is not shown in report - so i putted there the 1st date of the month in custom column) , then i appended all files
I created a column with date of 1st date in the specific month.
There can be also year/month.
i would like to count only consecutive months not days.
The problem is when there is turning year over ...
If latency is bigger than target for 2and more months in a row i would like to demonstrate as i showen above in the picture.
Nov 2021 - fail 1, Dec 2021 - fail 2, Jan 2022 - fail 3 (should show the fail as 3rd month in a row)
Hi Anonymous ,
Please create a date table and the relationship.
Then create these measures:
Measure = IF ( SUM ( 'Table'[Latency Value] ) > SUM ( 'Table'[Target value] ), 1, 0 )Measure2 = VAR tab = SUMMARIZE ( ALLSELECTED ( 'Date' ), 'Date'[Year_Month], "M", [Measure] ) VAR mindate = CALCULATE ( MAX ( 'Date'[Year_Month] ), ALLSELECTED ( 'Date' ), FILTER ( tab, 'Date'[Year_Month] < MAX ( 'Date'[Year_Month] ) && [M] = 0 ) ) RETURN IF ( [Measure] = 1, SUMX ( FILTER ( tab, 'Date'[Year_Month] <= MAX ( 'Date'[Year_Month] ) && 'Date'[Year_Month] > mindate ), [M] ) - 1 )If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Hello v-kkf-msft ,
unbelievable !!
This works just like a magic !
Thank you very much for your help ! 😊✊
