Amend Measure so start date is 9 months after based on First Date in table

[Anonymous]

Hi Experts

See sample file. 

https://www.dropbox.com/s/erp5exx8bbhi57v/Rolling%20%281%29.pbix?dl=0 

I want to amend my meaure in my table (see below) so my start date is 9 months after the first date in the table i.e First Date is (dec-18) - hence start date should be (Aug-19). All previous values to be shown as blank(zero).

Rolling 3 =

var _currentMth = MAX('Calendar'[Date])

var _month0Dates = DATESINPERIOD('Calendar'[Date],_currentMth,-3 ,MONTH)

var _mth0 = CALCULATE(sum(RollingData[Month 0]), _month0Dates )

var _mth1 = CALCULATE(sum(RollingData[Month 1]), DATEADD(_month0Dates,-1,MONTH) )

var _mth2 = CALCULATE(sum(RollingData[Month 2]), DATEADD(_month0Dates,-2,MONTH) )

var _mth3 = CALCULATE(sum(RollingData[Month 3]), DATEADD(_month0Dates,-3,MONTH) )

var _mth4 = CALCULATE(sum(RollingData[Month 4]), DATEADD(_month0Dates,-4,MONTH) )

var _mth5 = CALCULATE(sum(RollingData[Month 5]), DATEADD(_month0Dates,-5,MONTH) )

var _mth6 = CALCULATE(sum(RollingData[Month 6]), DATEADD(_month0Dates,-6,MONTH) )

return _mth0 + _mth1 + _mth2 + _mth3 + _mth4 + _mth5 +_mth6

[v-eqin-msft · Accepted solution]

Hi @Anonymous ,

Similar issue here: https://community.powerbi.com/t5/Desktop/Tricky-Rolling-3-and-6-Months/td-p/1942330

Based on my test, you need to delete the relationship between two tables firstly.

And then please try this measure:

3 mtn Rolling = 
VAR _LastDate =
    LASTDATE ( 'RollingData'[Month] )
VAR _0 =
    CALCULATE (
        SUM ( RollingData[Month 0] ),
        FILTER (
            ALL ( 'RollingData' ),
            [Month] <= _LastDate
                && [Month] > DATEADD ( _LastDate, -3, MONTH )
        )
    )
VAR _1 =
    CALCULATE (
        SUM ( RollingData[Month 1] ),
        FILTER (
            ALL ( 'RollingData' ),
            [Month] <= DATEADD ( _LastDate, -1, MONTH )
                && [Month] > DATEADD ( _LastDate, -4, MONTH )
        )
    )
VAR _2 =
    CALCULATE (
        SUM ( RollingData[Month 2] ),
        FILTER (
            ALL ( 'RollingData' ),
            [Month] <= DATEADD ( _LastDate, -2, MONTH )
                && [Month] > DATEADD ( _LastDate, -5, MONTH )
        )
    )
VAR _3 =
    CALCULATE (
        SUM ( RollingData[Month 3] ),
        FILTER (
            ALL ( 'RollingData' ),
            [Month] <= DATEADD ( _LastDate, -3, MONTH )
                && [Month] > DATEADD ( _LastDate, -6, MONTH )
        )
    )
VAR _4 =
    CALCULATE (
        SUM ( RollingData[Month 4] ),
        FILTER (
            ALL ( 'RollingData' ),
            [Month] <= DATEADD ( _LastDate, -4, MONTH )
                && [Month] > DATEADD ( _LastDate, -7, MONTH )
        )
    )
VAR _5 =
    CALCULATE (
        SUM ( RollingData[Month 5] ),
        FILTER (
            ALL ( 'RollingData' ),
            [Month] <= DATEADD ( _LastDate, -5, MONTH )
                && [Month] > DATEADD ( _LastDate, -8, MONTH )
        )
    )
VAR _6 =
    CALCULATE (
        SUM ( RollingData[Month 6] ),
        FILTER (
            ALL ( 'RollingData' ),
            [Month] <= DATEADD ( _LastDate, -6, MONTH )
                && [Month] > DATEADD ( _LastDate, -9, MONTH )
        )
    )
VAR _SUM = _0 + _1 + _2 + _3 + _4 + _5 + _6

RETURN

IF(DATEDIFF(MAX('Calendar'[Date]), MAX('RollingData'[Month]),MONTH)>=8,_SUM,BLANK())

The final output is shown below:

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.