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

 

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!
!! Subscribe to my youtube Channel !!

Helpful resources

Announcements
Power BI Show Ep 4 Post Show Carousel.jpg

The Power BI Community Show

Shabnam Watson demos Incremental refresh & Hybrid Tables and Leila Etaati demos Charticulator.

PBI April Release 2022 768x460.png

Check it out!

Click here to read more about the April 2022 updates!

Power BI Dev Camp Session 22 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!