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.sumx #dax.sameperiodlastyear #same.period.last.year

DAX | Same Period Last Year Calc - 1

LY Sales 1 =

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

#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.weekday

DAX | Week Day (Week Begins on Monday)

Weekday = 

WEEKDAY( [Date], 2 )

#ms.pbi #dax #dax.weeknum

DAX | Week No - 2

Week Number = 

WEEKNUM( [Date], 2 )

#ms.pbi #dax #dax.weekday

DAX | Week End Date (Week Begins on Monday)

Week End date = 

'Calendar'[Date] + 7-1 * WEEKDAY( 'Calendar'[Date], 2 )

#ms.pbi #dax #dax.weekday

DAX | Week Start Date - 2 (Week Begins on Monday)

Week Start date = 

'Calendar'[Date] + -1 * WEEKDAY( 'Calendar'[Date], 2 ) + 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 #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 #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.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 #table

DAX | Create Table - 2

Table =

{
	("Nik", 101),
	("Hafiz", 102),
}

#ms.pbi #dax #dax.datatable #dax.integer #dax.string #table

DAX | Create Table - 1

Table =

DATATABLE(
	"SNo", INTEGER,
	"Name", STRING,
	{
		{101, "Nik"},
		{102, "Hafiz"},
	}
)

#ms.pbi #dax #dax.topn #ranking #dax.format #dax.concatenatex

DAX | Best Selling Day/Date - 2

Best Selling Day/Date =

CONCATENATEX(
	TOPN(
		1,
		'Calendar',
		[Total Sales]
		),
	FORMAT( 'Calendar'[Date], "dd-mmm" )
	)

#ms.pbi #dax #dax.topn #ranking #dax.values

DAX | Best Selling Product

Top Selling Product =

TOPN(
	1,
	VALUES( Sales[ProductID] ),
	[Total Sales]
)

#ms.pbi #dax #dax.topn #ranking

DAX | Best Selling Day/Date - 1

Best Selling Day/Date =

TOPN(
	1,
	'Calendar',
	[Total Sales]
)

#ms.pbi #dax #calendar.table #dax.switch #dax.true #dax.month #quarter

DAX | Quarter No - 2

Qtr =

SWITCH(
	TRUE(),
	MONTH('Calendar[Date]') <=3, "Q1",
	MONTH('Calendar[Date]') <=6, "Q2",
	MONTH('Calendar[Date]') <=9, "Q3",
	"Q4"
)

#ms.pbi #dax #calendar.table #week.no

DAX | Year + WeekNo

YearWeekNo = 

'Calendar'[Year]*100 + 'Calendar'[WeekNo]

#ms.pbi #dax #calendar.table #dax.day

DAX | Day of Month No

DayofMonthNo =

DAY ( 'Calendar'[Date] )

#ms.pbi #dax #dax.weekday #calendar.table

DAX | Day of Week No

DayofWeekNo = 
WEEKDAY ( [Date], 2 )

#ms.pbi #dax #calendar.table #dax.format #dax.calendar #format-ddd

DAX | Day Name

DayName = 

FORMAT ( 'Calendar'[Date], "ddd" )

#ms.pbi #dax #calendar.table #dax.weeknum

DAX | Week No - 1

WeekNo = 

"W" & WEEKNUM( 'Calendar'[Date], 2 )

#ms.pbi #dax #calendar.table #dax.rankx #dax.asc #dax.dense #ranking #week.rank

DAX | Week Ranking

Week Rank = 

RANKX(
    ALL( 'Calendar' ),
    'Calendar'[Week StartDate], , ASC, Dense)

#ms.pbi #dax #calendar.table #dax.weekday

DAX | Week Start Date - 1 (Week Begins on Monday)

Week StartDate = 

'Calendar'[Date] + -1*WEEKDAY( 'Calendar'[Date], 2 ) + 1

#ms.pbi #dax #calendar.table #dax.weekday

DAX | Week End Date

Week EndDate = 

'Calendar'[Date] + 7-1*WEEKDAY( 'Calendar'[Date], 2 )

#ms.pbi #dax #calendar.table #dax.quarter

DAX | Quarter No - 1

QtrNo = 

"Q" & QUARTER( 'Calendar'[Date] )

#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

#ms.pbi #dax #dax.lookupvalue

DAX | Lookup Value from Rows in Unrelated Tables

LOOKUPVALUE(
    Products[UnitPrice],
    PRODUCTS[ProductID], ORDER_DETAILS[ProductID]
        )

#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 }
    )
)

#ms.pbi #power.query #date #earliest.date #ms.excel

Power Query - M | Create A Dynamic Date Table In Power Query

Earliest Date

= #date(Date.Year(List.Min(Source[OrderDate])),1,1)

Latest Date

= #date(Date.Year(List.Max(Source[RequiredDate])),12,31)

(Dynamic) Calendar
let
    Source = {Number.From(pmEarliestDate)..Number.From(pmLatestDate)},
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}})
in
    #"Changed Type"

#dax #tabular.editor #live.dataset #ms.pbi #dataset

Showing DAX Expressions When Using Power BI Datasets

foreach (var m in Model.AllMeasures) {
  m.Description = m.Expression;
}

DAX | Sales % Based on Selected Item/Brand

DAX | Same Period Last Year Calc - 2

DAX | Same Period Last Year Calc - 1

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 Day (Week Begins on Monday)

DAX | Week No - 2

DAX | Week End Date (Week Begins on Monday)

DAX | Week Start Date - 2 (Week Begins on Monday)

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 | Current Week (CW) Calc

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

DAX | Day-on-Day (DoD) Calc

DAX | Previous Day (PD) Calc

DAX | Rank Sales Rep based on Total Sales

DAX | Create Table - 2

DAX | Create Table - 1

DAX | Best Selling Day/Date - 2

DAX | Best Selling Product

DAX | Best Selling Day/Date - 1

DAX | Quarter No - 2

DAX | Year + WeekNo

DAX | Day of Month No

DAX | Day of Week No

DAX | Day Name

DAX | Week No - 1

DAX | Week Ranking

DAX | Week Start Date - 1 (Week Begins on Monday)

DAX | Week End Date

DAX | Quarter No - 1

DAX | Creating Calendar Table Using CALENDARAUTO

DAX | Lookup Value from Rows in Unrelated Tables

DAX | Calculate Revenue Across Multiple Years

Power Query - M | Create A Dynamic Date Table In Power Query

Showing DAX Expressions When Using Power BI Datasets

Save snippets that work with our extensions