cancel
Showing results for
Did you mean:
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.

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
Community Support

Hi,

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

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:

Hope this helps.

Best Regards,

Giotto Zhi

3 REPLIES 3
Community Support

Hi,

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

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:

Hope this helps.

Best Regards,

Giotto Zhi

Helper IV

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.

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://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!

Announcements