cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AlejandroPCar
Helper IV
Helper IV

Cumulative total in fixed period

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. 

 

Capture.PNG

 

 

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

1 ACCEPTED SOLUTION
v-gizhi-msft
Community Support
Community Support

Hi,

 

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

100.PNG

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:

101.PNG

Here is my test pbix file:

pbix 

Hope this helps.

 

Best Regards,

Giotto Zhi

 

View solution in original post

3 REPLIES 3
v-gizhi-msft
Community Support
Community Support

Hi,

 

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

100.PNG

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:

101.PNG

Here is my test pbix file:

pbix 

Hope this helps.

 

Best Regards,

Giotto Zhi

 

View solution in original post

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
Super User
Super User

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/

 

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Top Solution Authors
Top Kudoed Authors