Rolling by month

[azakir]

Hi Guys. 

I have the following formulas: 

Waste To Landfill Graph2_V1 = (CALCULATE([Waste to Landfill (Tonnes)_V1] * 0.95, DateDim[Previous Year] = 1))/12

Total_V2 =

VAR CurrentDate = MAX ( DateDim[Date] )

RETURN

CALCULATE (

    [Waste To Landfill Graph2_V1] ,

    FILTER (

        ALL ( DateDim ),

        DateDim[Date] <= CurrentDate

        && DateDim[Previous Year] = 1

    )

)

This gives me the following table: 

What I am after is how can I roll the total of Total_V2. For example, I would like Jan to be 695, Feb to be 695*2, Mar to be 695*3 and so on. 

Is there any way I can apply a rolling DAX Calculation to any of the measures?

[v-yangliu-msft · Accepted solution]

Hi  @azakir ,

You can try the following dax.

MEASURE =
VAR _monthnumber =
    MONTH ( MAX ( 'DateDim'[Date] ) )
VAR CurrentDate =
    MAX ( DateDim[Date] )
VAR _sum =
    CALCULATE (
        [Waste To Landfill Graph2_V1],
        FILTER (
            ALL ( DateDim ),
            DateDim[Date] <= CurrentDate
                && DateDim[Previous Year] = 1
        )
    )
VAR _round =
    ROUNDDOWN ( _sum, 0 )
RETURN
    _round * _monthnumber

Best Regards,

Liu Yang

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

[azakir]

Hi @v-yangliu-msft This did the trick. Thank you

[Ashish_Mathur]

Hi,

What are you trying to achieve?  Why should Feb be 695.06*2 and so on?

[azakir]

Hi @Ashish_Mathur . Thanks for the reply. What I am trying to achieve is the total 695 being rolled up every month. So the requirement is whatever the total was last year (695 in this case) make it a target for this year with every month being rolled up by the same total.

[Ashish_Mathur]

Share the download link of the PBI file.

[azakir]

@Ashish_Mathur  there you go: https://file.io/2FHyiU2Jh50y 

[Ashish_Mathur]

You already are getting the correct result via the Total_V3 measure. What help do you need?

[azakir]

If you notice, I am just multiplying it by the month number. I was wondering if there is a more efficient way to use a rolling DAX measure. 

[Ashish_Mathur]

Hi,

File deleted from the link you shared earlier.