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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
eduardosilvin3
Helper II
Helper II

Weekly filters

Helllo all, I need to add filter with the date (year, month and day) for my 4 graphs: Daily, previous day, current week, previous week.

So picking 15th november 2023 for example.

The results I need in the graphs:

Daily  - nov 15 results

previous day - nov 14 results

Week to date – because our weeks start on a Monday it would be nov 13 to nov 19 results (15 because is the last day)

previous week  – nov 6 to nov 12 results

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@eduardosilvin3 , You can use these measures

 

Based on today, use all ('Date'), in case you want remove any other filter from date

 

Today = CALCULATE([Net], FILTER('Date','Date'[Date] = Today() ) )
Yesterday = CALCULATE([Net], FILTER('Date','Date'[Date] = Today()-1 ) )
Same day Last week Today= CALCULATE([Net], FILTER('Date','Date'[Date] = Today()-7 ) )

 



Based on filter or row context

This Day = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])))
Last Day = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])-1))
Last Day = CALCULATE(sum('Table'[Qty]), previousday('Date'[Date]))

 

Have these new columns in Date Table, Week Rank is Important in Date/Week Table

Week Rank = RANKX('Date','Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX('Date','Date'[Year Week],,ASC,Dense) //YYYYWW format


These measures can help
This Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))

 

WTD = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank]) && 'Date'[WeekDay]<=max('Date'[WeekDay])))
LWTD = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1 && 'Date'[WeekDay]<=max('Date'[WeekDay]) ))

WTD = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank]) && 'Date'[WeekDay]<=max('Date'[WeekDay])-1))
LWTD = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1 && 'Date'[WeekDay]<=max('Date'[WeekDay])-1 ))



 

 

WTD Today =
var _min = TODAY() -WEEKDAY(TODAY(),2) +1 //Monday week start
var _max = today()
return CALCULATE([Net], FILTER('Date','Date'[Date] >=_min && 'Date'[Date] <= _max))

LWTD Today =
var _min = TODAY() -WEEKDAY(TODAY(),2) -6 //Monday week start
var _max = today() -7
return CALCULATE([Net], FILTER('Date','Date'[Date] >=_min && 'Date'[Date] <= _max))

 

Cheat Sheet — Power BI Time Intelligence Formulas Using Today
https://medium.com/chandakamit/cheat-sheet-power-bi-time-intelligence-formulas-using-today-654f26e27...

 

Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8
Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@eduardosilvin3 , You can use these measures

 

Based on today, use all ('Date'), in case you want remove any other filter from date

 

Today = CALCULATE([Net], FILTER('Date','Date'[Date] = Today() ) )
Yesterday = CALCULATE([Net], FILTER('Date','Date'[Date] = Today()-1 ) )
Same day Last week Today= CALCULATE([Net], FILTER('Date','Date'[Date] = Today()-7 ) )

 



Based on filter or row context

This Day = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])))
Last Day = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])-1))
Last Day = CALCULATE(sum('Table'[Qty]), previousday('Date'[Date]))

 

Have these new columns in Date Table, Week Rank is Important in Date/Week Table

Week Rank = RANKX('Date','Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX('Date','Date'[Year Week],,ASC,Dense) //YYYYWW format


These measures can help
This Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))

 

WTD = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank]) && 'Date'[WeekDay]<=max('Date'[WeekDay])))
LWTD = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1 && 'Date'[WeekDay]<=max('Date'[WeekDay]) ))

WTD = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank]) && 'Date'[WeekDay]<=max('Date'[WeekDay])-1))
LWTD = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1 && 'Date'[WeekDay]<=max('Date'[WeekDay])-1 ))



 

 

WTD Today =
var _min = TODAY() -WEEKDAY(TODAY(),2) +1 //Monday week start
var _max = today()
return CALCULATE([Net], FILTER('Date','Date'[Date] >=_min && 'Date'[Date] <= _max))

LWTD Today =
var _min = TODAY() -WEEKDAY(TODAY(),2) -6 //Monday week start
var _max = today() -7
return CALCULATE([Net], FILTER('Date','Date'[Date] >=_min && 'Date'[Date] <= _max))

 

Cheat Sheet — Power BI Time Intelligence Formulas Using Today
https://medium.com/chandakamit/cheat-sheet-power-bi-time-intelligence-formulas-using-today-654f26e27...

 

Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8
Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510

So sorry for the delay, I was in another projects! I followed with difficulties, but I found your PBI file and WOW, life saver! I saved it in my Onedrive as "Dates YOU NEED THIS" in case someday I forget about it! thanks again!

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors