thiscodeWorks - Organizing the best of code online

#ms.pbi #dax #dax.var #dax.return #week.to.date-wtd #dax.lastdate #dax.max #dax.weekday #dax.calculate #dax.datesbetween #dax.dateadd

DAX | Week-to-Date (WTD) - 1

Sales-WTD = 

VAR CD =
    LASTDATE( 'Calendar'[Date] )

VAR CY =
    MAX( 'Calendar'[Year] )
    
VAR WeekDayNo =
    WEEKDAY( LASTDATE( 'Calendar'[Date] ), 3 )

RETURN
CALCULATE(
    [Total Sales],
    DATESBETWEEN(
        'Calendar'[Date],
        DATEADD(
            CD, 
            -1 * WeekDayNo, DAY ), 
            CD),
    'Calendar'[Year] = CY
)

#ms.pbi #dax #current.week #previous.week #dax.var #dax.return #dax.if #dax.blank #dax.divide #week.on.week.%-wow%

DAX | Week-on-Week % (WoW%) Calc

Sales-WoW% = 

VAR WoW =
    [Sales-WoW]
    
VAR PW = 
    [Sales-PW]
    
RETURN
    IF (
        PW = BLANK (),
            BLANK (),
        DIVIDE ( WoW, PW )
        )

#ms.pbi #dax #current.week #previous.week #dax.var #dax.return #dax.if #dax.blank #week.on.week-wow

DAX | Week-on-Week (WoW) Calc

Sales-WoW = 

VAR WoW =
    IF ( [Sales-PW] = BLANK(),
        BLANK(),
    [Sales-CW] - [Sales-PW] )

RETURN
    WoW

#ms.pbi #dax #dax.calculate #dax.filter #dax.all #current.week #previous.week #dax.var #dax.return #dax.sumx #dax.&& #current.year #week.no #calendar #max.week #dax.selectedvalue

DAX | Previous Week (PW) Calc - 1 (taking into consideration year crossover)

Sales-PW = 

VAR CurrentWeek =
    SELECTEDVALUE( 'Calendar'[WeekNo] )

VAR CurrentYear =
    SELECTEDVALUE( 'Calendar'[Year] )

VAR MaxWeekNo =
    CALCULATE(
        MAX ( 'Calendar'[WeekNo] ), 
            ALL ( 'Calendar' )
            )

RETURN
SUMX(
    FILTER( ALL ( 'Calendar' ),
        IF ( CurrentWeek = 1,
            'Calendar'[WeekNo] = MaxWeekNo && 'Calendar'[Year] = CurrentYear -1,
            'Calendar'[WeekNo] = CurrentWeek -1 && 'Calendar'[Year] = CurrentYear )
        ),
    [Total Sales]
    )

#ms.pbi #dax #previous.day #dax.calculate #dax.var #dax.return #dax.if #day.on.day-dod #day.on.day.%-dod% #dax.blank

DAX | Day-on-Day % (DoD%) Calc

Sales-DoD% = 

VAR DoD =
    [Sales-DoD]
    
VAR PD = 
    [Sales-PD]
    
RETURN
    IF (
        PD = BLANK (),
            BLANK (),
        DIVIDE ( DoD, PD )
        )

#ms.pbi #dax #previous.day #dax.calculate #dax.var #dax.return #dax.if #day.on.day-dod #dax.blank

DAX | Day-on-Day (DoD) Calc

Sales-DoD = 

VAR DoD =
    IF ( [Sales-PD] = BLANK(),
        BLANK(),
    [Total Sales] - [Sales-PD] )

RETURN
    DoD

#ms.pbi #dax #calendar.table #dax.addcolumns #dax.var #dax.return #dax.format #dax.month

DAX | Creating Calendar Table Using CALENDARAUTO

Calendar = 
  
VAR CalTable = CALENDARAUTO ()
VAR MyCal =
    ADDCOLUMNS (
        CalTable,
        "Index", MONTH ( [Date] ),
        "Month", FORMAT ( [Date], "mmm" ),
        "Qtr", FORMAT ( [Date], "\QQ" ),
        "Year", FORMAT ( [Date], "yyyy" )
    )
RETURN
    MyCal

DAX | Week-to-Date (WTD) - 1

DAX | Week-on-Week % (WoW%) Calc

DAX | Week-on-Week (WoW) Calc

DAX | Previous Week (PW) Calc - 1 (taking into consideration year crossover)

DAX | Day-on-Day % (DoD%) Calc

DAX | Day-on-Day (DoD) Calc

DAX | Creating Calendar Table Using CALENDARAUTO

Save snippets that work with our extensions