We can use measures to have an easy comparison on the value of today and yesterday, current month and last month. However, it isn’t easy when we encounter below situations:
If you need to compare the values of 2020 and last year at this time, but next time you need to compare the values of 2020 and 2018, you need to modify all measures that contain Year, or recreate these measures. When we have 100 measures, it will be a tedious work.
If you need to compare two periods that might have a different number of days, like comparing one month against a full year. Filter will be fixed in the formula and cannot be dynamically controlled by the slicer.
I will show you two ways to dynamically compare the value of two periods by slicer.
Create two tables contain Date column for two period’s value and don’t create relationship between Date table and fact table:
Date1 = VALUES(Sales[OrderDate])
Date2 = VALUES(Sales[OrderDate])
Create the following measures(TREATAS applies the result of a table expression as filters to columns from an unrelated table):