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

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/

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 !! Announcements #### Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world. #### Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st! #### What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better. Top Solution Authors
Top Kudoed Authors
Users online (3,552)