Cumulative total in fixed period

[AlejandroPCar]

Hi guys 

I need some help here. I want to calculate a cumulative sum but between a fixed period of 12 months. Like the following example, I made. 

The idea is, for example, I want to start in May 2018 so the cumulative total starts in May 2018 and ends 12 months later in April 2019. After that, in May 2019 the cumulative starts again and then finish 12 months later. Of course, the idea is that happens whatever the month I need to start. 

I tried with the following formula with no success. 

12month_test = 
    CALCULATE(
        [monthly_value],
        DATESINPERIOD(
            Dates[Dates],
            LASTDATE(Dates[Dates), -12, MONTH
        )
    )

I hope you can help me to solve this. 

Thank you

[v-gizhi-msft · Accepted solution]

Hi,

According to your description, i create a sample to test:

Please follow these steps:

1)Create a slicer table:

Slicer Table = DISTINCT(SELECTCOLUMNS('Table',"Month",'Table'[Date].[Month],"MonthNo",'Table'[Date].[MonthNo]))

2)Try this measure:

Measure = 
IF (
    MAX ( 'Table'[Month] ) = SELECTEDVALUE ( 'Slicer Table'[Month] ),
    MAX ( 'Table'[Monthly Value] ),
    CALCULATE (
        SUM ( 'Table'[Monthly Value] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Date] <= MAX ( 'Table'[Date] )
                && 'Table'[Date]
                    >= IF (
                        MIN ( 'Table'[MonthNo] ) < MIN ( 'Slicer Table'[MonthNo] ),
                        DATE ( YEAR ( MIN ( 'Table'[Date] ) ) - 1, MIN ( 'Slicer Table'[MonthNo] ), 1 ),
                        DATE ( YEAR ( MIN ( 'Table'[Date] ) ), MIN ( 'Slicer Table'[MonthNo] ), 1 )
                    )
        )
    )
)

3)Create a check measure:

check = 
IF (
    MAX ( 'Table'[Date] )
        < DATE ( MINX ( ALLSELECTED ( 'Table' ), 'Table'[Date].[Year] ), CALCULATE (
            MAX ( 'Slicer Table'[MonthNo] ),
            FILTER (
                'Slicer Table',
                'Slicer Table'[Month] = SELECTEDVALUE ( 'Slicer Table'[Month] )
            )
        ), 1 ),
    0,
    1
)

4)Choose above [Measure] and [Year],[Month],[Monthly Value] as a table visual.

   Apply the above [check] to this visual by setting [check]=1.

   Choose [Month] from slicer table as a slicer.

   When select one month in slicer, the result shows:

Here is my test pbix file:

pbix 

Hope this helps.

Best Regards,

Giotto Zhi

[AlejandroPCar]

Thank you @v-gizhi-msft 

Your solution was really useful. 

Als thank you @amitchandak I found some of your formulas very handy for other issues I had.

[amitchandak]

You can use datesytd and totalytd with end date with date Calendar

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"4/30"))
This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD((ENDOFYEAR('Date'[Date])),"4/30"))

Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"4/301"))
Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"4/30"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"4/30"))

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

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/