topic % of base year by month in DAX Commands and Tips

% of base year by month

UR982T — Thu, 04 Feb 2021 00:18:57 GMT

How could I possibly calculate % of base year using 2019 as my base year but on a monthly basis? That is, Jan 2020 / Jan 2019, Feb 20 / Feb 2019, Mar 20 / Mar 19 and so on up to Jan 2021 / Jan 2019? I've done a Measure for 2019 using Sales of 2019 = CALCULATE( [TOTAL SALES], 'Dates'[Year] = 2019] and then Percent of 2019 = DIVIDE( [Total Sales], [Sales of 2019] ), but this only works at the year level. Once I use month on the rows of my table only the monthly values for 2019 are populated and so I don't get a percent of base in 2020 or 2021 as the monthly sales values of those years don't have the corresponding 2019 values next to them in the table. Any ideas how I can accomplish this?

As the image below the 2019 monthly values are being repeated against 2020/2021 and tyring to create a visual of % of 2019.

Re: % of base year by month

wdx223_Daniel — Thu, 04 Feb 2021 00:18:11 GMT

Sales of 2019 = CALCULATE( [TOTAL SALES], 'Dates'[Year] = 2019,all(dates))

Re: % of base year by month

UR982T — Thu, 04 Feb 2021 00:23:02 GMT

That gives me the total sales of 2019 against each month. I need the sales value of each month of 2019 paired against its corresponding month in 2020 and 2021. I've shared an excel table to make it clearer!

Re: % of base year by month

UR982T — Thu, 04 Feb 2021 03:52:02 GMT

I've changed it to this and it works:

Sales of 2019 by Month =
CALCULATE(
[TOTAL SALES]
, FILTER( ALL('Dates'[Date]), 'Dates' [Year] = 2019
&& 'Dates'[Month Number] = MAX( 'Dates'[Month Number])
)