cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Junaid11
Helper IV
Helper IV

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 :

jdfkj.PNG
Your help would be quite useful for me.
Thanks
1 ACCEPTED SOLUTION
sevenhills
Super User
Super User

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-La...

 

 

View solution in original post

4 REPLIES 4
MFelix
Super User
Super User

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

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



sevenhills
Super User
Super User

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-La...

 

 

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.

sadeffeef.PNGsdffgg.PNG

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

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!