thiscodeWorks - Organizing the best of code online

#ms.pbi #dax #dax.allselected #dax.calculate #dax.divide

DAX | Sales % Based on Selected Item/Brand

Sales % =

DIVIDE (
	[Total Sales],
	CALCULATE (
		[Total Sales],
		ALLSELECTED ( 'Product'[Brand])
		)
)

#ms.pbi #dax #dax.sameperiodlastyear #same.period.last.year #dax.calculate

DAX | Same Period Last Year Calc - 2

LY Sales 2 =

CALCULATE( 
	[Total Sales],
	SAMEPERIODLASTYEAR( 'Calendar'[Date] )
	)

#ms.pbi #dax #dax.calculate #dax.filter #dax.all #dax.max #last.week.to.date-lwtd

DAX | Last Week-to-Date (LWTD) - 2

Sales-LWTD = 

CALCULATE(
    [Sales-WTD],
    FILTER( 
        ALL( 'Calendar'),
        'Calendar'[Week Rank] = MAX( 'Calendar'[Week Rank] )-1 
        )
)

#ms.pbi #dax #dax.calculate #dax.filter #dax.all #dax.max #last.week.to.date-lwtd

DAX | Last Week-to-Date (LWTD) - 1

Sales-LWTD = 

CALCULATE( [Total Sales]), 
	FILTER( ALL( 'Calendar'),
	'Calendar'[Week Rank] = ( MAX ('Calendar'[Week Rank]) -1) && 
	'Calendar'[Weekday] <= < MAX( 'Calendar'[Weekday])
		)
	)

#ms.pbi #dax #dax.calculate #dax.filter #dax.all #dax.max #week.to.date-wtd

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

Sales-WTD = 

CALCULATE( [Total Sales], 
	FILTER( ALL('Calendar'),
		'Calendar'[Week Rank] = MAX( 'Calendar'[Week Rank] ) && 
		'Calendar'[Weekday] <= MAX( 'Date'[Weekday] ) 
		) 
	)

#ms.pbi #dax #dax.calculate #dax.filter #dax.all #dax.max

DAX | Previous Week (PW) Calc - 2

Sales-PW =

CALCULATE ( [Total Sales], 
	FILTER( ALL( 'Calendar' ),
	'Calendar'[Week Rank] = MAX ( 'Calendar'[Week Rank] ) -1 ) 
	)

#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 #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 #dax.calculate #current.week #dax.filter #dax.all #week.rank

DAX | Current Week (CW) Calc

Sales-CW = 

CALCULATE( [Total Sales], 
    FILTER(
        ALL('Calendar'),
        'Calendar'[Week Rank] = MAX( 'Calendar'[Week Rank])
            )
        )

#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 #previous.day #dax.calculate #dax.dateadd

DAX | Previous Day (PD) Calc

Sales-PD =

CALCULATE( 
    [Total Sales],
    DATEADD( 'Calendar'[Date], -1, DAY )
    )

#ms.pbi #dax #dax.calculate #dax.filter #dax.all #dax.in

DAX | Calculate Revenue Across Multiple Years

CALCULATE(
    [Revenue],
    FILTER(
        ALL( 'Calendar'[Year] ),
        'Calendar'[Year] IN { 1996, 1998 }
    )
)

DAX | Sales % Based on Selected Item/Brand

DAX | Same Period Last Year Calc - 2

DAX | Last Week-to-Date (LWTD) - 2

DAX | Last Week-to-Date (LWTD) - 1

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

DAX | Previous Week (PW) Calc - 2

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

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

DAX | Current Week (CW) Calc

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

DAX | Day-on-Day (DoD) Calc

DAX | Previous Day (PD) Calc

DAX | Calculate Revenue Across Multiple Years

Save snippets that work with our extensions