Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
Solved! Go to Solution.
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:
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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsRead 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:
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.
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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsUser | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |