Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi All,
I have a function calculating sales yesterday which is
Solved! Go to Solution.
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.
Hi,
In your Table visual, assuming you have dragged Year/Month and date from Date Table, this measure should work
=CALCULATE(SUM(Sales[salesamount]),sameperiodpastyear('Date'[Date]))
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.
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
CheckLeapDate
Output:
User | Count |
---|---|
97 | |
87 | |
77 | |
67 | |
63 |
User | Count |
---|---|
111 | |
96 | |
96 | |
67 | |
63 |