cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper II

## Cumulative total for previous month using custom non-date slicer

Hi!

Have been struggling with this for a long time now. 😞

I have current week, current month, previous week and previous month as a custom slicer in a disconnected table and one measure

to return results for each of the chosen periods. Everything works except for Previous month.

The part of the entire code which corresponds to Previous month below.

This link contains the sample file with all info needed, Sample file

SELECTEDVALUE ( 'Period slicer'[Period] ) = "Previous month",
VAR MaxDate =
EOMONTH ( MAX ( Period[Date] ), -1 ) //Returns the last day of previous month, 2021-12-31
VAR LastMonth =
EOMONTH ( MaxDate, -1 ) + 1 //Returns the first day of previous month, making the period 2021-12-01 to 2021-12-31. The variables return the correct timeframe, but do not work in the visual.
RETURN
CALCULATE (
[Number of loaded tonnes],
ALL ( Period[Date] ),
Period[Date] <= MaxDate,
Period[Date] >= LastMonth
)

1 ACCEPTED SOLUTION
Super User

@KrisD , for previous month cumulative use only datesmtd with date add

CALCULATE (
[Number of loaded tonnes],
DATESMTD (

DATEADD ( Period[Date], -1, MONTH )
)
)

Endofmonth will give complete month on all dates

4 REPLIES 4
Super User

@KrisD , You can use date table and time intelligence

refer example

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
next month Sales = CALCULATE(SUM(Sales[Sales Amount]),nextmonth('Date'[Date]))
this month = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH('Date'[Date])))
last MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
previous month value = CALCULATE(sum('Table'[total hours value]),previousmonth('Date'[Date]))

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
Power BI — Qtr on Qtr with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw

Helper II

However I do not get the desired results from the code.

CALCULATE (
[Number of loaded tonnes],
DATESMTD (
ENDOFMONTH (
DATEADD ( Period[Date], -1, MONTH )
)
)
)

Super User

@KrisD , for previous month cumulative use only datesmtd with date add

CALCULATE (
[Number of loaded tonnes],
DATESMTD (

DATEADD ( Period[Date], -1, MONTH )
)
)

Endofmonth will give complete month on all dates

Helper II

Thank you so much, looks like it works !!

Will definitely save your code examples, have obviously been overthinking the way I calculate these kind of measures.

As a side note, are you able to explain to me why my code with the EOMONTH variables do not work the way I thought they would?