DAX | Current Week (CW) Calc

PHOTO

Sat Dec 30 2023 04:54:52 GMT+0000 (Coordinated Universal Time)

Saved by @nikahafiz #ms.pbi #dax #dax.calculate #current.week #dax.filter #dax.all #week.rank

Sales-CW = 

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

COPY

Save snippets that work from anywhere online with our extensions

Comments

dax

@nikahafiz

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

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

#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 #dax #dax.lookupvalue

DAX | Lookup Value from Rows in Unrelated Tables

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

#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 #calendar.table #dax.quarter

DAX | Quarter No - 1

QtrNo = 

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

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

DAX | Week No - 1

WeekNo = 

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

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

DAX | Day Name

DayName = 

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

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

DAX | Day of Week No

DayofWeekNo = 
WEEKDAY ( [Date], 2 )

DAX | Current Week (CW) Calc

Save snippets that work from anywhere online with our extensions

Comments

More like this

dax

Browse more snippets >>

DAX | Current Week (CW) Calc

Save snippets that work from anywhere online with our extensions

Comments

More like this

dax

Browse more snippets >>

Embed code snippet