Forum Discussion
Dax consecutive Count by months
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 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)
Yes, the logic is 2+ month
- mahoneypat
Microsoft Employee
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
Hello Pat,
i think i dont get it.
I don't know where are you dragging those 3700Please 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
