Last Year to Date by Day - Non Standard Calendar

DAX DAX Calculations
1

Hi Guys,

Trying to figure out Sales up until today against a non standard calendar. I’m using the variable combined with the selectedvalue DAX approach but can’t get it just right.

What I’ve achieved so far is if the week is a previous sum up until the end of the week and if it’s the current week then sum up until today, however if i select a day on the report page the figures in the table don’t interact.
If i selected a day in the past or current week I want the figure to show the stats for that day. I could try to build the logic with more SWITCH layers but wondered if any of you guys know a better way.

Sales LYTWTD  (Table) = 
VAR CurrentYear VALUE(LOOKUPVALUE(Vw_DimMBCalendar[YearFlag],Vw_DimMBCalendar[ActualDate],TODAY()))
VAR CurrentWeek = VALUE(LOOKUPVALUE(Vw_DimMBCalendar[Week],Vw_DimMBCalendar[ActualDate],TODAY()))
VAR SelectedYear = SELECTEDVALUE(Vw_DimMBCalendar[YearFlag] )
VAR SelectedWeek = SELECTEDVALUE(Vw_DimMBCalendar[Week])
VAR CurrentDayofweek = VALUE(WEEKDAY(TODAY(),1))

RETURN

SWITCH(TRUE(),
SelectedWeek = CurrentWeek &&
SelectedYear = CurrentYear,
CALCULATE([Total Selling],
    FILTER(
        ALL(Vw_DimMBCalendar),
        Vw_DimMBCalendar[YearFlag]       = CurrentYear -1  &&
        Vw_DimMBCalendar[Week]             = CurrentWeek   &&
        Vw_DimMBCalendar[DayOfWeek] <= CurrentDayofweek) 
            ),
        
 CALCULATE([Total Selling],
        FILTER(
        ALL(Vw_DimMBCalendar),
        Vw_DimMBCalendar[YearFlag] = SelectedYear -1  &&
        Vw_DimMBCalendar[Week]       = SelectedWeek   
)))
2

After taking a step back figured it out. Didn’t fully understand the ALLSELECTED formula, looks like i needed to include the ISFILTERED formula for it too take into account the day selected.

The formula below worked. Hope you guys find it useful, took me forever to figure out.

Sales LYTWTD (Table) = 
VAR CurrentYear = VALUE(LOOKUPVALUE(Vw_DimMBCalendar[YearFlag],Vw_DimMBCalendar[ActualDate],TODAY()))
VAR CurrentWeek = VALUE(LOOKUPVALUE(Vw_DimMBCalendar[Week],Vw_DimMBCalendar[ActualDate],TODAY()))
VAR SelectedYear = SELECTEDVALUE(Vw_DimMBCalendar[YearFlag] )
VAR SelectedWeek = SELECTEDVALUE(Vw_DimMBCalendar[Week])
VAR CurrentDayofweek = VALUE(WEEKDAY(TODAY(),1))
VAR Selecteddayofweek= SELECTEDVALUE(Vw_DimMBCalendar[DayOfWeek])

RETURN

SWITCH(TRUE(),
SelectedWeek = CurrentWeek &&
SelectedYear = CurrentYear  &&
not(ISFILTERED(Vw_DimMBCalendar[Short DayName]))
,
CALCULATE([Total Selling],
    FILTER(
        ALL(Vw_DimMBCalendar),
        Vw_DimMBCalendar[YearFlag]       = CurrentYear -1  &&
        Vw_DimMBCalendar[Week]             = CurrentWeek   &&
        Vw_DimMBCalendar[DayOfWeek] <= CurrentDayofweek) 
            )
,

ISFILTERED(Vw_DimMBCalendar[Short DayName]),
CALCULATE([Total Selling],
    FILTER(
        ALL(Vw_DimMBCalendar),
        Vw_DimMBCalendar[YearFlag] = SelectedYear -1  &&
        Vw_DimMBCalendar[Week] = SelectedWeek   &&
        Vw_DimMBCalendar[DayOfWeek] = Selecteddayofweek) -- then show the day stats
           )

    , 
CALCULATE([Total Selling],
    FILTER(
        ALL(Vw_DimMBCalendar),
        Vw_DimMBCalendar[YearFlag] = SelectedYear -1  &&
        Vw_DimMBCalendar[Week] = SelectedWeek   
            )
))
3

Nice one thanks for sharing.

Chrs

1 Like