Forum Discussion

Mark_Timson's avatar
Mark_Timson
Helper I
4 years ago

DAX Query - Temp Table Filter Measure - Previous Year Sum

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 follow...
Need Help
v-jingzhang's avatar
v-jingzhang
Community Support
4 years ago

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's avatar
Mark_Timson
Helper I
4 years ago

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's avatar
    v-jingzhang
    Community Support
    4 years ago

    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