Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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 Valu...
consecutive
  • v-kkf-msft's avatar
    v-kkf-msft
    4 years ago

    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.

mahoneypat's avatar
mahoneypat
Icon for Microsoft Employee rankMicrosoft Employee
4 years ago

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's avatar
Anonymous
Not applicable
4 years ago

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
OriginDestinationLatency ValuePacket Loss ValueJitter ValueDateKeyTarget value  ConsecutiveStatus
Site 1Site 1114.7800.0101.11.2021Site 1Site 117.4  0OK
Site 1Site 1114.1100.0201.12.2021Site 1Site 117.4  1Watch out
Site 1Site 1114.1100.0201.01.2022Site 1Site 117.4  2Watch out
Site 1Site 1114.1100.0201.03.2022Site 1Site 117.4  0OK
Site 1Site 1114.1100.0201.05.2022Site 1Site 117.4  0OK
Site 1Site 1114.1100.0201.06.2022Site 1Site 117.4  1Watch out
  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      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