-I have a report with 3 columns (Date, System Sales, System Sales LY)
-The goal is to compare Day to Day Sales from this year to Last year.
-I have used the basic DAX for the Sales calculation
System Sales = SUM(Salestable[Total])
System Sales LY = CALCULATE ([System Sales], SAMEPERIODLASTYEAR('DateDimension'[Date])
-My 'Sales table' is joined to the 'Date Dimension table' on SalesDate and Date respectively.
-My problem is Feb2020. It is a leap year. So for LY calculation, DAX is auto-populating for '2/29'. Even though there is no '2/29' in 2019. (Please see the attached screenshot for reference.).
- In LY System Sales column, for the '2/29' should be empty, however, it shows the value of '2/28'. (In this case, you can see the value $449,003.6 repeating twice)
How do I fix it? Can you please help me?
Solved! Go to Solution.
Sales PY := if( hasonevalue(DateDimension[Date]), var __currentDate = max(DateDimension[Date]) var __shouldCalculate = month(__currentDate) <> 2 && day(__currentDate) <> 29 var __result = if(__shouldCalculate, CALCULATE( [System Sales], DATEADD(DateDimension[Date],-1,YEAR) ) ) return __result )
Im curious if you would instead use the DATEADD opposed to SPLY if it would be any different. I have had issues with SPLY in the past and since have used DATEADD instead.
Lets try this first - as SPLY may have logic built in for leap year.. which i suppose folks off of leap year would like to know what they made last year if there was a leap year for the 28th... anywho this is a silly oversight imo by microsoft possibly.
Double check your data to ensure there are no values for 02/29/2019 - I understand this date technically doesnt exist..
Rev. LY = CALCULATE( [TOTAL REV], DATEADD( dDATES, -1 , YEAR) )
let me know if there are any differences.
Hi Alex, Thanks for the quick response.
-I have tried the DATEADD. It gave me the same outcome as the 'Sameperiodlastyear'
Check out new user group experience and if you are a leader please create your group!
Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.
Click here to read more about the July 2021 Updates
Did you know that you can visit the Power Query Forum in Power BI and now Power Apps