Dax consecutive Count by months

[Anonymous]

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

[v-kkf-msft · Accepted solution]

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.

[Anonymous]

Hello all,
i have one more question.
Is it possible to use this measure also as a slicer ( see in the picture below)
I created new table where i would like to filter by "Status" - the statuses will be only
"Breached 2+" and unfiler (means "All")

[Anonymous]

Hello @v-kkf-msft ,
unbelievable !! 
This works just like a magic !

Thank you very much for your help ! 😊✊

[mahoneypat]

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

[Anonymous]

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)

[Anonymous]

Yes, the logic is 2+ month

[mahoneypat]

Since your data were an image (couldn't copy/paste), I used a model I user for demos, but here is a DAX pattern that you can adapt.

Two Months Over Target =
VAR thismonthindex =
MIN( 'Date'[Months from Now] )
VAR prev2monsummary =
CALCULATETABLE(
ADDCOLUMNS( DISTINCT( 'Date'[Months from Now] ), "cSales", [Total Sales] ),
ALL( 'Date' ),
'Date'[Months from Now]
IN { thismonthindex, thismonthindex - 1 }
)
RETURN
IF(
COUNTROWS( FILTER( prev2monsummary, [cSales] > 3700 ) ) = 2,
"Watch Out",
"OK"
)

Note that I used the Date table I mentioned previously found here (that has the Months From Now column).

No Sort Date Tables! – Hoosier BI

Pat

[Anonymous]

Hello Pat,
i think i dont get it.
I don't know where are you dragging those 3700

Please see the data for copy paste below

										My desired consecutive
Origin	Destination	Latency Value	Packet Loss Value	Jitter Value	Date	Key	Target value			Consecutive	Status
Site 1	Site 11	14.78	0	0.01	01.11.2021	Site 1Site 11	7.4			0	OK
Site 1	Site 11	14.11	0	0.02	01.12.2021	Site 1Site 11	7.4			1	Watch out
Site 1	Site 11	14.11	0	0.02	01.01.2022	Site 1Site 11	7.4			2	Watch out
Site 1	Site 11	14.11	0	0.02	01.03.2022	Site 1Site 11	7.4			0	OK
Site 1	Site 11	14.11	0	0.02	01.05.2022	Site 1Site 11	7.4			0	OK
Site 1	Site 11	14.11	0	0.02	01.06.2022	Site 1Site 11	7.4			1	Watch out

[mahoneypat]

Did you add the Date table to your model? The 3700 is just a value that would replicate your scenario with the mock dataset I used.  You would replace the table/column names for your date, and 3700 with your target value.

Pat

[Anonymous]

Hello Pat,
i tried to use also your code but when the year turns over it is not showing me the right data.
Please see below.
Do you also have an idea how to put the measure as a slicer and let the text "Watch out" in the column ? 
I tried many oprions but without success