Need Best Method for Comparing Values between Dates

[TotunG]

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.

[123abc]

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:

1. 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
)
)

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.

[TotunG]

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. 

[123abc]

I understand your need for custom comparisons between specific months (e.g., August and March). To achieve this, you can create a custom logic in your DAX measures. Here's how you can create measures that compare values for specific months:

1. Create a Date Table: Ensure you have a date table in your Power BI model with a relationship to your fact table.

2. Create a Measure for August (P8): You can create a measure that calculates values for August (P8). This measure will allow you to compare it with another measure for March (P3).

AugustValue = CALCULATE( [YourMeasure], FILTER( ALL('Date Table'), MONTH('Date Table'[Date]) = 8 ) )

Create a Measure for March (P3): Similarly, create a measure for March (P3).

MarchValue = CALCULATE( [YourMeasure], FILTER( ALL('Date Table'), MONTH('Date Table'[Date]) = 3 ) )

Create a Comparison Measure: Now, create a measure that compares the values for August and March.

AugustVsMarchComparison = [AugustValue] - [MarchValue]

With these measures in place, you can easily compare the values for August and March, and the calculation will not be affected by the slicer selection. You can adjust the logic in the measures to suit your specific needs for custom comparisons between different months or periods.

If issue still not solved please shared sample file.

[v-zhangti]

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.