Forum Discussion
Need Best Method for Comparing Values between Dates
Hi,
Usually when I compare values its often between months, years etc. To achieve this, I usually do a CALCULATE, SUM measure and make a filter like MAX(Month) = MAX Month -1 to get the previous month's values for example. However, this does not interact well with say a Month slicer as the measure will always go to the latest month (say August or the number 😎 and do -1 to get 7 which then returns nothing as this is being filtered.
Is there a better method to use e.g. OFFSET perhaps to maintain filter interactivity with comparisons. Therefore, users can dynamically refer to the latest prev. month to their latest month selected?
Any guidance would be appreciated.
4 Replies
To achieve comparisons between months while maintaining filter interactivity in Power BI, you can use DAX functions like SAMEPERIODLASTYEAR or DATESYTD. These functions allow you to calculate values for the same period in the previous year or the year-to-date values, respectively, while respecting slicer selections and maintaining interactivity.
Here's how you can do it:
Same Period Last Year Comparison:
Use the SAMEPERIODLASTYEAR function to calculate values for the same period in the previous year. This works well with slicers because it automatically adapts to the selected time period. For example, if you have a Month slicer, it will compare the selected month with the same month in the previous year.
Sales LY =
CALCULATE(
[Total Sales],
SAMEPERIODLASTYEAR('Calendar'[Date])
)Year-to-Date (YTD) Comparison:
Use the DATESYTD function to calculate values for the year-to-date period. This is useful when you want to compare the values from the beginning of the year up to the selected month.
Sales YTD =
CALCULATE(
[Total Sales],
DATESYTD('Calendar'[Date])
)Custom Comparison Period:
If you want to compare with a custom period (e.g., the previous month regardless of the slicer selection), you can use a measure that calculates the value for the selected period and another measure that calculates the value for the custom comparison period.
Sales This Month =
CALCULATE(
[Total Sales],
FILTER(
ALL('Calendar'),
'Calendar'[Date] = MAX('Calendar'[Date])
)
)Sales Last Month =
CALCULATE(
[Total Sales],
FILTER(
ALL('Calendar'),
'Calendar'[Date] = MAX('Calendar'[Date]) - 1
)
)In this example, "Sales This Month" calculates the sales for the selected month, and "Sales Last Month" calculates the sales for the previous month.
Using these DAX functions and measures, you can create dynamic comparisons that interact well with slicers, allowing users to select different time periods and see the relevant comparisons.
please provide sample data if issue not solved.
This was very fast and almost AI like? But thank you for the help.
Custom comparisons are what would work best as for example say I have August (referred to as P8) and March (referred to as P3) comparisons required. However, using the CALCULATE SUM MAX - 1 method does not work as P8 (or the no.8) - 1 = 7 and therefore returns nothing.
Hi, TotunG
Can you provide sample data for testing? Sensitive information can be removed in advance. What kind of expected results do you expect? You can also show it with pictures or Excel.
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
