DAX Query - Temp Table Filter Measure - Previous Year Sum

[Mark_Timson]

Hi Community,

Im looking to create a Temp Table In Dax that collects a date range and be able to specify such range in lower measure, Is there a way to do this? 

The code I have is the following:

DEFINE
var shortdayToday = 
    CALCULATE(
        DISTINCT('Calendar'[Short Day Name]), 
        'Calendar'[date] = TODAY()
    )
var DateToday = 
    CALCULATE(
        DISTINCT('Calendar'[date]), 
        'Calendar'[date] = TODAY()
    )    
//EVALUATE {DateToday}


--DEFINE 
  TABLE datesTY =
  SUMMARIZECOLUMNS(
          'Calendar'[Date],
          'Calendar'[Short Day Name],
          FILTER('Calendar','Calendar'[LYInWeekFinFlag] = 1),
          FILTER('Calendar','Calendar'[Short Day Name] = shortdayToday)
      )
 EVALUATE
 datesTY

This gets me my Temp Table with Date and Day

I then want to filter my Measure accordingly like this :

EVALUATE
--DEFINE
    --MEASURE 'Retail Sales - Hourly'[TY Todays Sales] =
        CALCULATE (
            SUM ( 'Sales'[Value] ),
            'Calendar'[Date] = MAX('datesTY'[date]),
            'Calendar'[Short Day Name] = shortdayToday,
            'Time'[Up To Hour] = 1,
        )
EVALUATE
SUMMARIZECOLUMNS(
    'Branch'[Full Region Name],
    'Calendar'[Date],
    "TY Today Sales",[TY Todays Sales]
)
ORDER BY 'Branch'[Full Region Name]

However this errors in Dax studio, the reason behind this is I cant use time intelligence functions to do same day last year as the Day Returned is not the same day when you do this. The Date is the same but the day last year is different. Any Help would be appreciated. 

Thanks!

Mark

[v-jingzhang]

Hi @Mark_Timson 

As 365/7=52.14, the day last year always doesn't have the same shortday as today. If you want a date with the same shortday, it should be 52*7=364 days ago. Then you could try

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-364,DAY)

The problem is that there is one day offset. When a year is a leap year, the offset will be 2 days. Then you need to adjust the offset days in dateadd function every several years. 

Maybe you can consider adding an ISO-Week-Number column in Calendar table. Use the same ISO-Week-Number and the same WeekDay(shortday) to get the same date last year. Something like 

Same Date Last Year =
CALCULATE (
    MAX ( 'Calendar'[Date] ),
    FILTER (
        ALL ( 'Calendar' ),
        'Calendar'[IsoWeekNumber] = WEEKNUM ( TODAY (), 21 )
            && 'Calendar'[WeekDay] = WEEKDAY ( TODAY () )
            && 'Calendar'[Year]
                = YEAR ( TODAY () ) - 1
    )
)

How to calculate ISO week number and ISO year in DAX - Data Cornering

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[Mark_Timson]

Hi Community Support Team _ Jing

Yes we experienced the same leap year issue in our org and its a pain for this reason. We have a calendar table that is updated regularly with Flags to specify which week we are in marked as a 1, We do this for the same week last year (Flag marked as 2) and 2 years ago (Flag Marked as 3) for each row in the calendar table by day. 

So I made use of this with the following dax. 

var shortdayToday = 
    CALCULATE(
        DISTINCT('Calendar'[Short Day Name]), 
        'Calendar'[date] = TODAY()
    )
var DateToday  = 
    CALCULATE(
        DISTINCT('Calendar'[date]), 
        'Calendar'[date] = TODAY()
    )   
    
var DateLY = 
    CALCULATE(
        DISTINCT('Calendar'[date]), 
        FILTER('Calendar','Calendar'[LYInWeekFlag] = 2),
        FILTER('Calendar','Calendar'[Short Day Name] = shortdayToday)
    )
RETURN
    CALCULATE (
        SUM ( 'Sales - Hourly'[Sale Value] ),
        FILTER('Calendar','Calendar'[Short Day Name] = shortdayToday),
        FILTER('Calendar','Calendar'[Date] = DateLY),
        'Time'[Up To Hour] = 1
     )

I wanted to make use of DAX Define table which would shorten the code but couldnt use in pbi or aas measure definition so had to make do with variables. While this supports my problem it would be easier if there was a function that we could use for same day LY. 

[v-jingzhang]

Hi @Mark_Timson 

It seems your current dax could be shortened into below as DateToday variable is never used in the following part and if 'Calendar'[date] is unique in Calendar table. 

var shortdayToday = 
    CALCULATE(
        DISTINCT('Calendar'[Short Day Name]), 
        'Calendar'[date] = TODAY()
    )
var DateLY = 
    CALCULATE(
        DISTINCT('Calendar'[date]), 
        FILTER('Calendar','Calendar'[LYInWeekFlag] = 2),
        FILTER('Calendar','Calendar'[Short Day Name] = shortdayToday)
    )
RETURN
    CALCULATE (
        SUM ( 'Sales - Hourly'[Sale Value] ),
        FILTER('Calendar','Calendar'[Date] = DateLY),
        'Time'[Up To Hour] = 1
     )

Regards,

Jing

[Mark_Timson]

Hi Amit, 

This code would get the same Date for Previous Year, however the Day is different Today is 07/12/2021 this would get Tuesday for Last year this was Monday 07/12/2020 The Requirement is to get the same Days Sales Which would be Tuesday. Maybe its just my organisation but there needs to be a SameDayLastYear Function in Dax for this requirement. 

[amitchandak]

@Mark_Timson , for same day last year, have you tried like ?

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),SAMEPERIODLASTYEAR('Date'[Date]))