Forum Discussion
Dax Function to Calculate same day last year
- 4 years ago
There are a variety of ways to do this but the most straightforward might be to compute the date explicitly.
Sales Yesterday LY = VAR _Y = TODAY () - 1 VAR _YLY = DATE ( YEAR ( _Y ) - 1, MONTH ( _Y ), DAY ( _Y ) ) RETURN CALCULATE ( SUM ( Sales[salesamount] ), 'Date'[Date] = _YLY )Note: I'm not sure if this would work if yesterday were a leap day.
There are a variety of ways to do this but the most straightforward might be to compute the date explicitly.
Sales Yesterday LY =
VAR _Y = TODAY () - 1
VAR _YLY = DATE ( YEAR ( _Y ) - 1, MONTH ( _Y ), DAY ( _Y ) )
RETURN
CALCULATE ( SUM ( Sales[salesamount] ), 'Date'[Date] = _YLY )
Note: I'm not sure if this would work if yesterday were a leap day.
- MarkLaf4 years ago
Super User
I was curious about the question on leap day, so did a quick test. Using time intelligence functions will keep result in February, whereas DATE will convert to March 1. See below.
Also, Nanakwame, check out DAX Guide for how to use SAMEPERIODLASTYEAR. They explain how to use in CALCULATETABLE to return mutliple dates. You can see how I use it in CALCULATE below to return a single date/value. Also included DATEADD version for those interested in a method than can be modified for conversions other than last year.
(note, the output is a table so needs to be pasted into a New Table formula for those interested in testing in PBI desktop)
LastYearTest = // For the below to work, set up another table first // with CALENDAR( DATE( 2024, 1, 1 ), DATE( 2024, 12, 31 ) ) // and mark it as a date table VAR BaseCal = GENERATE( CALENDAR( DATE( 2024, 1, 1 ), DATE( 2024, 12, 31 ) ), //2024 is a leap year VAR _dt = [Date] //Designated date table's _dt equivalent for time intelligence funcs VAR _dt_T = CALCULATETABLE( Dates, TREATAS( { _dt }, Dates[Date] ) ) VAR _MinusYear = DATE ( YEAR ( _dt ) - 1, MONTH ( _dt ), DAY ( _dt ) ) VAR _SamePeriodLastYear = CALCULATE( SAMEPERIODLASTYEAR( Dates[Date] ), _dt_T ) VAR _DateAdd = CALCULATE( DATEADD( Dates[Date], -1, YEAR ), _dt_T ) RETURN ROW( "MinusYear",_MinusYear, "SamePeriodLastYear",_SamePeriodLastYear, "DateAdd", _DateAdd ) ) VAR CheckLeapDate = FILTER( BaseCal, [Date] >= DATE(2024, 2, 28) && [Date] <= DATE(2024, 3, 1) ) RETURN CheckLeapDateOutput:
- Nanakwame4 years ago
Helper II
Thanks AlexisOlson
That worked.