Forum Discussion

sparkymark75's avatar
sparkymark75
Frequent Visitor
7 years ago

Sum count of rows for prior date range

I have data which when I can get into this form using a summarize;

 

 

VAR Product = SELECTEDVALUE(Products[GenericDescription])
VAR Module = SELECTEDVALUE(ModuleFaults[new_moduletype])
VAR FailType = SELECTEDVALUE(ModuleFaults[new_fault])

VAR FailuresGrouped = SUMMARIZE(
                ALL('ModuleFaults'),
                Incident[CompletedMonthYear],
                Incident[ITMGEDSC],
                ModuleFaults[new_moduletype],
                ModuleFaults[new_fault],
                "MonthlyFailures",
                COUNTROWS(ModuleFaults)
            )

 

 

I'm trying to create a measure which will show a sum of the MonthlyFailures column (per Widget, Module, Fault) for each MonthYear row for the previous 3 months. So the output should look like so...


 

 

 

 

I've tried doing a SUMX but I just keep getting the total number of rows back.

 

 

VAR FailCountByType = SUMX(
        FILTER(FailuresGrouped,
            Incident[CompletedMonthYear] >= FIRSTDATE(DATEADD('Incident'[CompletedMonthYear], -3, MONTH)) &&
            Incident[CompletedMonthYear] <= EOMONTH(Incident[CompletedMonthYear], 0) &&
            ModuleFaults[new_moduletype] = Module &&
            ModuleFaults[new_fault] = FailType &&
            Incident[ITMGEDSC] = Product
        ),
    [MonthlyFailures]
)

 

 

Need Help

3 Replies

  • v-frfei-msft's avatar
    v-frfei-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    Hi sparkymark75 ,

     

    One sample for your reference. Please check the following steps as below.

     

    1. To create a calculated column in your summrized table.

    rank = 
RANKX ( ALL ( 'Table1' ), Table1[Month Year],, ASC, DENSE )

    2. To create a measure to get the excepted result we need.

    Measure 2 = 
VAR ran =
    MAX ( Table1[rank] ) - 2
RETURN
    CALCULATE (
        SUM ( Table1[MonthFailures] ),
        FILTER (
            ALLSELECTED ( Table1 ),
            Table1[rank] >= ran
                && Table1[rank] <= MAX ( Table1[rank] )
        )
    )

    Pbix as attached.

     

    • sparkymark75's avatar
      sparkymark75
      Frequent Visitor
      7 years ago

      Thanks for replying. I'm guessing I didn't word my question as clearly as I should have. The summary table is calculated inline in the measure as a variable, e.g.

       

      VAR FailuresGrouped = SUMMARIZE(
                FILTER(ALL('ModuleFaults'), ModuleFaults[CaseCompletedOn] <> BLANK()),
                Incident[CompletedMonthYear],
                Incident[ITMGEDSC],
                ModuleFaults[new_moduletype],
                ModuleFaults[new_fault],
                "MonthlyFailures",
                COUNTROWS(ModuleFaults)
            )

      Is it possible to use the RANKX on a table in a variable and then the subsequent measure code you have? 

      • v-frfei-msft's avatar
        v-frfei-msft
        Icon for Community Support rankCommunity Support
        7 years ago

        Hi sparkymark75 ,

         

        ADDCOLUMNS should work, please have a try. Could you please share your sample data to me ?