thiscodeWorks - Organizing the best of code online

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

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.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 #dax.ranks #dax.all #ranking

DAX | Rank Sales Rep based on Total Sales

Sales Rep Rank =

RANKX(
	ALL( Sales[Sales Rep]),
	[Total Sales],
	Dense
)

//Dense = not skip

#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 | 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 | Previous Week (PW) Calc - 1 (taking into consideration year crossover)

DAX | Current Week (CW) Calc

DAX | Rank Sales Rep based on Total Sales

DAX | Calculate Revenue Across Multiple Years

Save snippets that work with our extensions