Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Junaid11
Helper V
Helper V

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.