Toggling between yesterday and month-to-date data with a 445 calendar date table

[APM]

Hello,

I'm having a really hard time delivering something my company needs for daily reporting (and feeling the pressure! I'm entirely new to BI). I would really appreciate the help.

I am building a dashboard in which sales data is pulled in and populates the below fields:

What I need to build - a slicer in which I can select:

• Yesterday
• Last week (Sunday-Saturday format)
• Month-to-Date 

...and the corresponding data would populate the visual for that time frame.

This is complicated by the fact that we use a 445 calendar, and always compare Mondays to Mondays and Tuesdays to Tuesdays (etc) when looking at the data YOY.

So, If I select "yesterday", it will show 6/8/2021 data under This Year Sales and 6/9/2020 data under LY Sales. For month, it would show 5/30-6/8 for 2021 and 5/31-6/9 for 2020. I already built the 445 date table (formula is below the post), but need help building the slicer.

Any ideas? Thanks!

Date table formula:

Date = VAR vSundays =

{

DATE ( 2020, 04, 05 ),

DATE ( 2021, 04, 04 ),

DATE ( 2022, 04, 03 ),

DATE ( 2023, 04, 03 )

}

VAR vCalendar =

CALENDAR ( MINX ( vSundays, [Value] ), MAXX ( vSundays, [Value] ) - 1 )

VAR vAddDay =

ADDCOLUMNS (

vCalendar,

"Day",

VAR vFirstDOY =

MAXX ( FILTER ( vSundays, [Value] <= [Date] ), [Value] )

RETURN

DATEDIFF ( vFirstDOY, [Date], DAY ) + 1

)

VAR vAddYearWeek =

ADDCOLUMNS (

vAddDay,

"Year",

YEAR ( MAXX ( FILTER ( vSundays, [Value] <= [Date] ), [Value] ) ) + 1,

"Week", ROUNDUP ( [Day] / 7, 0 )

)

VAR vAddMonth =

ADDCOLUMNS (

ADDCOLUMNS (

vAddYearWeek,

"MonthNum",

SWITCH (

TRUE (),

[Week] <= 4, 4,

[Week] <= 8, 5,

[Week] <= 13, 6,

[Week] <= 17, 7,

[Week] <= 21, 8,

[Week] <= 26, 9,

[Week] <= 30, 10,

[Week] <= 34, 11,

[Week] <= 39, 12,

[Week] <= 43, 1,

[Week] <= 47, 2,

[Week] <= 52, 3

)

),

"Month", FORMAT ( DATE ( 2020, [MonthNum], 1 ), "MMMM" )

)

RETURN

vAddMonth

..and what the date table looks like:

[v-stephen-msft · Accepted solution]

Hi @APM ,

The easiest way is to use relative date slicer: https://docs.microsoft.com/en-us/power-bi/visuals/desktop-slicer-filter-date-range.

Also you can write DAX to solve: https://www.linkedin.com/pulse/custom-range-date-slicer-power-bi-dynamic-defaults-more-a%C5%9F%C4%B1...

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.