SAMEPERIODLASTYEAR AND DATEADD DIFFERENT RESULTS FOR DAY/MONTH

[Educate]

I am trying to calculate YoY% in my financial reports using the sameperiodlastyear and dateadd DAX syntax. When I show the sales by day the calculation, it is correct and matches up to the same day last year. However, when I change the days to months or years, there is a difference in the results. The dax results are not matching the same month last year nor the same year for the previous year. I tried the same with the DATEADD syntax and the same output as the sameperiodlastyear. Can someone help with this discrepancy? My formula is below:

SAMEPERIODLY =

    CALCULATE(

        'Sales'[Sales],

        SAMEPERIODLASTYEAR('Calendar'[DATE]),ALL('Calendar'[DATE]))

[v-jialongy-msft · Accepted solution]

Hi @Educate 

In the formula you provided, you're calculating sales for the same period last year by using 'SAMEPERIODLASTYEAR' . However, there are a couple of points to consider:

1. Context of the Calculation: The 'SAMEPERIODLASTYEAR' function works based on the current context of the report or visual. When you're looking at daily data, it matches exactly the same day last year. However, when you aggregate to months or years, the context changes, and it might not behave as expected. This is because 'SAMEPERIODLASTYEAR' doesn't just take the same month of the previous year; it takes the exact date range from the previous year. For instance, if your current context is January 1st to January 31st, 2023, 'SAMEPERIODLASTYEAR' would reference January 1st to January 31st, 2022.
   
   

2. Using ALL Function: The use of ALL('Calendar'[DATE]) in your calculate function is removing the filter context from the 'Calendar'[DATE] column, which can lead to unexpected results, especially when you change the granularity of your report from days to months or years. This is because 'ALL' removes any filters on the 'Calendar'[DATE] column, potentially leading to a calculation over the entire dataset rather than the intended period.
   
   
   

To address the issue, consider the following adjustments:

1. Review Your Calendar Table: Ensure your calendar table is correctly set up, with no missing dates, and it spans the entire range of your data. The time intelligence functions rely heavily on a well-structured date table.
   
   

2. Adjust the Calculation for Different Granularities: You might need different measures or additional logic in your DAX formula to handle different aggregation levels (monthly, yearly). For example, when dealing with months, you might want to use a combination of 'YEAR' and ‘MONTH’ functions to compare the same month across different years.
   
   

3. Reconsider the Use of ALL: If your intention is to compare the same period last year regardless of any other filters applied, then using ‘ALL’ might be appropriate. However, if you want the calculation to respect other filters (like product categories, regions, etc.), you might need to adjust the use of ‘ALL’ in your formula.

Best Regards,

Jayleny

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-jialongy-msft · Accepted solution]

Hi @Educate 

In the formula you provided, you're calculating sales for the same period last year by using 'SAMEPERIODLASTYEAR' . However, there are a couple of points to consider:

1. Context of the Calculation: The 'SAMEPERIODLASTYEAR' function works based on the current context of the report or visual. When you're looking at daily data, it matches exactly the same day last year. However, when you aggregate to months or years, the context changes, and it might not behave as expected. This is because 'SAMEPERIODLASTYEAR' doesn't just take the same month of the previous year; it takes the exact date range from the previous year. For instance, if your current context is January 1st to January 31st, 2023, 'SAMEPERIODLASTYEAR' would reference January 1st to January 31st, 2022.
   
   

2. Using ALL Function: The use of ALL('Calendar'[DATE]) in your calculate function is removing the filter context from the 'Calendar'[DATE] column, which can lead to unexpected results, especially when you change the granularity of your report from days to months or years. This is because 'ALL' removes any filters on the 'Calendar'[DATE] column, potentially leading to a calculation over the entire dataset rather than the intended period.
   
   
   

To address the issue, consider the following adjustments:

1. Review Your Calendar Table: Ensure your calendar table is correctly set up, with no missing dates, and it spans the entire range of your data. The time intelligence functions rely heavily on a well-structured date table.
   
   

2. Adjust the Calculation for Different Granularities: You might need different measures or additional logic in your DAX formula to handle different aggregation levels (monthly, yearly). For example, when dealing with months, you might want to use a combination of 'YEAR' and ‘MONTH’ functions to compare the same month across different years.
   
   

3. Reconsider the Use of ALL: If your intention is to compare the same period last year regardless of any other filters applied, then using ‘ALL’ might be appropriate. However, if you want the calculation to respect other filters (like product categories, regions, etc.), you might need to adjust the use of ‘ALL’ in your formula.

Best Regards,

Jayleny

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Educate · Accepted solution]

Thank you so much for this explanation. It helps me to go back and review my calendar table.

[amitchandak]

@Educate , no need to use all, Make sure, visual, slicer and measure use period from date table ,when needed

SAMEPERIODLY =
CALCULATE(
'Sales'[Sales],
SAMEPERIODLASTYEAR('Calendar'[DATE]) )

Above should work

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5b...
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[Educate]

I did remove the ALL but still getting the same result. I am using a date column from the calendar table. When I use the Day field to the column for every single day, it calculates correct. When I switch the Day field with month or year, the amounts are not correct. I am validaing by looking at the total sales amount from the same month/year of the prior year and my measure shows a different amount. What could be causing this?