Rolling total for only the current month

[Kpham]

For my use case I need to calculate the rolling total for the last 12 months, however I only need the rolling total when the currentmonth is this month. I figured out to create a rolling total measure for each month: 

Amount to be delivered Running Total = 
                CALCULATE(
                    SUM(Milestones[Amount To Be Delivered (€)]),
                    FILTER(ALLSELECTED(Milestones),
                    Milestones[DLV ZRP Month.DLV ZRP Month Level 01.Key] <=MAX(Milestones[DLV ZRP Month.DLV ZRP Month Level 01.Key])
                    ))

And this works!

Next step I'm checking if its the current month and post the result in a column:

IsCurrentMonth = 
    IF (
        YEAR 
        (Milestones[Delivery Date.Delivery Date Level 01.Key].[Date]) = YEAR ( TODAY () )
            && MONTH ( Milestones[Delivery Date.Delivery Date Level 01.Key].[Date] ) = MONTH ( TODAY () ),
        TRUE(),
        FALSE()
    )

  Then I'm creating a new column to select if it should be the month value or the rolling total

Running total = if(Milestones[IsCurrentMonth]=TRUE, 
            Milestones[Amount to be delivered Running Total],
Milestones[Amount To Be Delivered (€)])

The non rolling total values are fine, but the rolling total is way too high. 328 times higher then I expect.
What am i doing wrong?

[amitchandak]

@Kpham , try rolling like this with date table and then use if on top of it

example

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-12,MONTH))
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX(Sales[Sales Date]),-12,MONTH))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.

[Kpham · Accepted solution]

Hi thanks, so I have added a date dimension and i see some improvement. However I run into another related issue. 
So my rolling total works fine until current month -1.  The rolling total for the current month is exactly the same input value for june. And the accumilation starts in august again(July+August). 

Could tell me whats happening here and how I can solve this. Your advise is much appreciated

Amount to be delivered Running Total = 
                CALCULATE(
                    SUM(Milestones[Amount To Be Delivered (€)]),
                    FILTER(ALLSELECTED('DateDim'),
                    'DateDim'[Date Month] <=MAX('DateDim'[Date Month])
                    ))

[mahoneypat]

Do you have more than 12 months of data?  Your rolling total measure is not constrained to 12 months (just < max).   You list the first one as a measure but then mention columns.  Are you trying to add columns or measures (that you then use in a Table visual)?

Also, FYI that it is best practice list measures in your formula w/o the Table name (column names do have Table names, so we can more easily understand each other's DAX).  

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat