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?