Best practice for defaulting report to Current Month or Prior Month if 1st of Month

DAX DAX Calculations
1

Hi all,

I need to build a landing page to my report which will have cards with the following ‘Headline’ numbers:

Sales MTD, Sales MTD PY, Sales MTD vs Sales MTD PY
Sales YTD, Sales YTD PY, Sales YTD vs Sales YTD PY

Returns MTD, Returns MTD PY, Returns MTD vs Returns MTD PY
Returns YTD, Returns YTD PY, Returns YTD vs Returns YTD PY

The requirement is that the page will default based on today’s date and not require the user to change a Month Slicer to the current month. So…

If the user goes to the report on the 1st of Feb 2020, it needs to report January 2020 sales in MTD and Jan 2019 in MTD PY.
If the user goes to the report on the 10th of Feb 2020, it needs to report Feb 2020 sales in MTD and Feb 2019 in MTD PY.
If the user goes to the report 1st Jan 2020, it needs to report December 2019 sales in MTD and December 2018 sales in MTD PY.

I think there are two options, either calculate the required date range in each measure and use DATEBETWEEN or add a calculated column to my dates table IsMTDCY and ISMTDPY and set to Y based on today’s date and then filter each measure, where IsMTDCY = Y.

So for the calculated column approach I was thinking:

IsMTDCY = IF (YEAR (Dates[Date] ) = YEAR ( TODAY ()-1 ) && MONTH( Dates[Date] ) = MONTH( TODAY ()-1), “Yes”, “No”)

IsMTDPY = IF (YEAR (Dates[Date] ) = YEAR ( TODAY ()-1 )-1 && MONTH( Dates[Date] ) = MONTH( TODAY ()-1), “Yes”, “No”)

Before I go down one of these routes, I would like to check to see how other members would approach this? Would I be severely limiting myself with one over the other?

Thanks

Mark

2

Hi Mark,

Who’s the audience for this report? I’d probably put a card up for Last Month Total, and one for current MTD, and then the same cards for last year as well.

This way, you won’t be in a position to change slicers or use complex logic to determine the date and what to post in the cards.

1 Like
4

Hi @Mark, we’ve noticed that no response has been received from you since the 12th of February. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!