-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'