Forum Discussion

Medic7653's avatar
Medic7653
Regular Visitor
8 years ago
Solved

6 Month Moving Sum

New member and first time poster.   After a lot of research and reading, I am unable to resolve my issue.  I can easily create a moving sum of my volume, but I need months 1 - 5 to remain blank and...
  • MFelix's avatar
    MFelix
    8 years ago

    Hi Medic7653,

     

    Believe that you are using a filter or something similar to get the previous 6 months however taking into account the data the first rows also are consider in the calculations althoung for those specific rows it's not getting the full 6 months since they don't have enough rows. You need to do something like this:

     

    Moving 6 Months Sum =
VAR Month_selected =
    MAX ( 'Fact'[Month #] )
RETURN
    IF (
        Month_selected < 6;
        BLANK ();
        CALCULATE (
            SUM ( 'Fact'[Volume] );
            FILTER (
                ALL ( 'Fact'[Month #]; 'Fact'[Year]; 'Fact'[Month] );
                'Fact'[Month #]
                    >= Month_selected - 5
                    && 'Fact'[Month #] <= Month_selected
            )
        )
    )

    Regards

    MFelix

Medic7653's avatar
Medic7653
Regular Visitor
8 years ago

Apologies.  Here's my base formula for my measure:

Moving 6 Month Sum :=

CALCULATE(

SUM(Order_Details[Tot Sales]),

DATESINPERIOD(DimDate[Date],

LASTDATE(DimDate[Date]),

-6,MONTH)

)

  • OwenAuger's avatar
    OwenAuger
    Super User
    8 years ago

    Hi Medic7653

     

    Just double-checking - do you want your Moving Sum measure to return blank when fewer than 6 months are present in Order_Details?

    • Medic7653's avatar
      Medic7653
      Regular Visitor
      8 years ago

      OwenAuger-  yes, that is correct.

      • OwenAuger's avatar
        OwenAuger
        Super User
        8 years ago

        Medic7653

        This should be a pretty safe way of writing the measure:

         

        Moving 6 Month Sum (with 6 month check) :=
VAR DateFilter =
    DATESINPERIOD ( DimDate[Date], MAX ( DimDate[Date] ), -6, MONTH )
VAR DateFilterMin =
    MINX ( DateFilter, DimDate[Date] )
VAR DateFilterNumMonths =
    COUNTROWS (
        CALCULATETABLE (
            SUMMARIZE ( DimDate, DimDate[Year], DimDate[Month] ),
                    // Above could be changed to SUMMARIZE ( DimDate, DimDate[YearMonth] ) if that column exists
            DateFilter
        )
    )
VAR EarliestDateInData =
    CALCULATE ( MIN ( Order_Details[Date] ), ALL ( Order_Details ) )
RETURN
    IF (
        AND ( EarliestDateInData <= DateFilterMin, DateFilterNumMonths >= 6 ),
        CALCULATE ( SUM ( Order_Details[Tot Sales] ), DateFilter )
    )

         

        This may be overkill as far as the checks it performs:

        • Checks whether the earliest date in Order_Details is at least as early as the first date in the 6-month DimDate filter
        • Checks whether the 6-month DimDate filter actually includes at least 6 months (would only fail if DimDate didn't extend early enough)

        Regards,

        Owen