Helper V

4 years ago

Solved

WTD Slicer

Hello,

I have created MTD/QTD and YTD slicer in same slicer but wanted to have WTD slicer now. I did it by following below video:

https://www.youtube.com/watch?v=AdLDYohLeJc

The code I have used to obtain it is below as well:

MTD/QTD/YTD =

VAR TodayDate = TODAY()

VAR YearStart = CALCULATE(STARTOFYEAR(PuigWeeklySkuByStore_data2[Month Year]),YEAR(PuigWeeklySkuByStore_data2[Month Year]) = YEAR(TodayDate) )

VAR QuarterStart = CALCULATE(STARTOFQUARTER(PuigWeeklySkuByStore_data2[Month Year]),YEAR(PuigWeeklySkuByStore_data2[Month Year])=YEAR(TodayDate),QUARTER(PuigWeeklySkuByStore_data2[Month Year]) = QUARTER(TodayDate))

VAR MonthStart = CALCULATE(STARTOFMONTH(PuigWeeklySkuByStore_data2[Month Year]),YEAR(PuigWeeklySkuByStore_data2[Month Year]) = YEAR(TodayDate),MONTH(PuigWeeklySkuByStore_data2[Month Year]) = MONTH(TodayDate))

VAR Result =

UNION(

ADDCOLUMNS(

CALENDAR(YearStart,TodayDate),

"Selection", "YTD"

ADDCOLUMNS(

CALENDAR(QuarterStart,TodayDate),

"Selection","QTD"

ADDCOLUMNS(

CALENDAR(MonthStart,TodayDate),

"Selection","MTD"

)

RETURN

Result

I just want to add WTD value in it. It would be quite useful for me. My slicer looks like below :

Your help would be quite useful for me.

Thanks

Need Help

sevenhills

4 years ago

Read your video link comments, it has the code ... I used the same

var WeekStart = CALCULATE(TodayDate-WEEKDAY(TodayDate,2),YEAR(Data[Data])=YEAR(TodayDate))

Try if this works...

MTD/QTD/YTD = -- MTD/QTD/YTD/WTD

VAR TodayDate = TODAY()

VAR YearStart = CALCULATE(STARTOFYEAR(PuigWeeklySkuByStore_data2[Month Year]),YEAR(PuigWeeklySkuByStore_data2[Month Year]) = YEAR(TodayDate) )

VAR QuarterStart = CALCULATE(STARTOFQUARTER(PuigWeeklySkuByStore_data2[Month Year]),YEAR(PuigWeeklySkuByStore_data2[Month Year])=YEAR(TodayDate),QUARTER(PuigWeeklySkuByStore_data2[Month Year]) = QUARTER(TodayDate))

VAR MonthStart = CALCULATE(STARTOFMONTH(PuigWeeklySkuByStore_data2[Month Year]),YEAR(PuigWeeklySkuByStore_data2[Month Year]) = YEAR(TodayDate),MONTH(PuigWeeklySkuByStore_data2[Month Year]) = MONTH(TodayDate))

VAR WeekStart = CALCULATE(TodayDate-WEEKDAY(TodayDate,2),YEAR(PuigWeeklySkuByStore_data2[Month Year])=YEAR(TodayDate))


VAR Result =
     UNION(
          ADDCOLUMNS( CALENDAR(YearStart,TodayDate), "Selection", "YTD"),
          ADDCOLUMNS( CALENDAR(QuarterStart,TodayDate), "Selection","QTD"),
          ADDCOLUMNS( CALENDAR(MonthStart,TodayDate), "Selection","MTD"),
          ADDCOLUMNS( CALENDAR(WeekStart,TodayDate), "Selection","WTD")
     )

RETURN Result

Also, read this blog post:

https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123

4 Replies

sevenhills

Super User

4 years ago

Read your video link comments, it has the code ... I used the same

var WeekStart = CALCULATE(TodayDate-WEEKDAY(TodayDate,2),YEAR(Data[Data])=YEAR(TodayDate))

Try if this works...

MTD/QTD/YTD = -- MTD/QTD/YTD/WTD

VAR TodayDate = TODAY()

VAR YearStart = CALCULATE(STARTOFYEAR(PuigWeeklySkuByStore_data2[Month Year]),YEAR(PuigWeeklySkuByStore_data2[Month Year]) = YEAR(TodayDate) )

VAR QuarterStart = CALCULATE(STARTOFQUARTER(PuigWeeklySkuByStore_data2[Month Year]),YEAR(PuigWeeklySkuByStore_data2[Month Year])=YEAR(TodayDate),QUARTER(PuigWeeklySkuByStore_data2[Month Year]) = QUARTER(TodayDate))

VAR MonthStart = CALCULATE(STARTOFMONTH(PuigWeeklySkuByStore_data2[Month Year]),YEAR(PuigWeeklySkuByStore_data2[Month Year]) = YEAR(TodayDate),MONTH(PuigWeeklySkuByStore_data2[Month Year]) = MONTH(TodayDate))

VAR WeekStart = CALCULATE(TodayDate-WEEKDAY(TodayDate,2),YEAR(PuigWeeklySkuByStore_data2[Month Year])=YEAR(TodayDate))


VAR Result =
     UNION(
          ADDCOLUMNS( CALENDAR(YearStart,TodayDate), "Selection", "YTD"),
          ADDCOLUMNS( CALENDAR(QuarterStart,TodayDate), "Selection","QTD"),
          ADDCOLUMNS( CALENDAR(MonthStart,TodayDate), "Selection","MTD"),
          ADDCOLUMNS( CALENDAR(WeekStart,TodayDate), "Selection","WTD")
     )

RETURN Result

Also, read this blog post:

https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123

Junaid11

Helper V

4 years ago

Hello sevenhills MFelix ,

The solution is working in Data tab as I put the Dax that you sent it shows the WTD dates but I put it in the table it is showing other dates as well from previous weeks. I have attached pictures for you understanding. It should be showing dates of current week but it is still showing previous week data. Kindly help in understand what is causing it to do this.

MFelix

Super User

4 years ago

Hi Junaid11 ,

Try the following code:

MTD/QTD/YTD = 
VAR TodayDate =
    TODAY ()
VAR YearStart =
    CALCULATE (
        STARTOFYEAR ( PuigWeeklySkuByStore_data2[Month Year]),
        YEAR ( PuigWeeklySkuByStore_data2[Month Year] ) = YEAR ( TodayDate )
    )
VAR QuarterStart =
    CALCULATE (
        STARTOFQUARTER ( PuigWeeklySkuByStore_data2[Month Year] ),
        YEAR ( PuigWeeklySkuByStore_data2[Month Year] ) = YEAR ( TodayDate ),
        QUARTER ( PuigWeeklySkuByStore_data2[Month Year] ) = QUARTER ( TodayDate )
    )
VAR MonthStart =
    CALCULATE (
        STARTOFMONTH (  PuigWeeklySkuByStore_data2[Month Year] ),
        YEAR ( PuigWeeklySkuByStore_data2[Month Year] ) = YEAR ( TodayDate ),
        MONTH ( PuigWeeklySkuByStore_data2[Month Year] ) = MONTH ( TodayDate )
    )
VAR WeekStart =
    CALCULATE (
        Today()  - WEEKDAY(Today() , 2 ) + 1,
        YEAR ( PuigWeeklySkuByStore_data2[Month Year] ) = YEAR ( TodayDate ),
        MONTH ( PuigWeeklySkuByStore_data2[Month Year] ) = MONTH ( TodayDate )
    )


VAR Result =
    UNION (
        ADDCOLUMNS ( CALENDAR ( YearStart, TodayDate ), "Selection", "YTD" ),
        ADDCOLUMNS ( CALENDAR ( QuarterStart, TodayDate ), "Selection", "QTD" ),
        ADDCOLUMNS ( CALENDAR ( MonthStart, TodayDate ), "Selection", "MTD" ),
        ADDCOLUMNS ( CALENDAR ( WeekStart, TodayDate ), "Selection", "WTD" )
    )
RETURN
    Result

MFelix

Super User

4 years ago

Hi Junaid11 ,

To what I can see from the code you don't have a date value on you calendar the one value that you have is the PuigWeeklySkuByStore_data2[Month Year] that I assume is a single date for each month is this correct?

If you don't have the granularity of the day you cannot have a WTD value so for this you need to add it in the calculation it would be something similar to this:

MTD/QTD/YTD = 
VAR TodayDate =
    TODAY ()
VAR YearStart =
    CALCULATE (
        STARTOFYEAR ( PuigWeeklySkuByStore_data2[Month Year]),
        YEAR ( PuigWeeklySkuByStore_data2[Month Year] ) = YEAR ( TodayDate )
    )
VAR QuarterStart =
    CALCULATE (
        STARTOFQUARTER ( PuigWeeklySkuByStore_data2[Month Year] ),
        YEAR ( PuigWeeklySkuByStore_data2[Month Year] ) = YEAR ( TodayDate ),
        QUARTER ( PuigWeeklySkuByStore_data2[Month Year] ) = QUARTER ( TodayDate )
    )
VAR MonthStart =
    CALCULATE (
        STARTOFMONTH (  PuigWeeklySkuByStore_data2[Month Year] ),
        YEAR ( PuigWeeklySkuByStore_data2[Month Year] ) = YEAR ( TodayDate ),
        MONTH ( PuigWeeklySkuByStore_data2[Month Year] ) = MONTH ( TodayDate )
    )
VAR WeekStart =
    CALCULATE (
        MIN(PuigWeeklySkuByStore_data2[Month Year])  - WEEKDAY(MIN(PuigWeeklySkuByStore_data2[Month Year]) , 2 ) + 1,
        YEAR ( PuigWeeklySkuByStore_data2[Month Year] ) = YEAR ( TodayDate ),
        MONTH ( PuigWeeklySkuByStore_data2[Month Year] ) = MONTH ( TodayDate )
    )


VAR Result =
    UNION (
        ADDCOLUMNS ( CALENDAR ( YearStart, TodayDate ), "Selection", "YTD" ),
        ADDCOLUMNS ( CALENDAR ( QuarterStart, TodayDate ), "Selection", "QTD" ),
        ADDCOLUMNS ( CALENDAR ( MonthStart, TodayDate ), "Selection", "MTD" ),
        ADDCOLUMNS ( CALENDAR ( WeekStart, TodayDate ), "Selection", "WTD" )
    )
RETURN
    Result

Forum Discussion

WTD Slicer

4 Replies

Related content

WTD?

WTD Verus last WTD

WTD for current Week

Slicer

Slicer issue.