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
Anonymous
Not applicable

Date filter to display data of yesterday except on Monday then it needs to display data of Friday

Hey guys,

For my dashboards I use formula to display data of yesterday, today and tomorrow. But when it's Monday, like today, yesterday has no data since there are no data entries for weekend days. I would like to make it so that when the day is Monday and I want to display data of yesterday that it displays data of Friday instead. I use the following calculated column formula In a table as well as dynamic date filter of PowerBI itself to get the data of yesterday, it depends on the diagram. 

 

The calculated column I use to get data of "Today", "Tomorrow" and " Yesterday" :

Days = IF(S_info_3days[Correct_delivery_date] = TODAY()-1;"Yesterday";IF(S_info_3days[Correct_delivery_date] = TODAY();"Today";IF(S_info_3days[Correct_delivery_date] = TODAY()+1;"Tomorrow")))
 
In different diagrams where I don't need the actual words today etc I use dynamic date formatting to pick the date that is in last 1 day without including today. So not sure how to make it work there, I'm guessing with some sort of measure. I think I need to make a calculated column that attaches a weekday number to my correct_delivery_date so I can make an if statement that sais if the week day = 2 then display week day 5 but I have data of multiple months so how do i make sure that it doesn't display last months friday but previous weeks friday. I'm kinda stuck with this and would like some help
1 ACCEPTED SOLUTION
Anonymous
Not applicable

hi @dax 

Ok so I found a solution for my context well for category 2. I adapted the formula in the following way:

 
Days = IF(WEEKDAY(TODAY();1) = 2;
IF(S_info_3days[Correct_delivery_date] = TODAY()-3;"Yesterday";IF(S_info_3days[Correct_delivery_date] = TODAY();"Today";IF(S_info_3days[Correct_delivery_date] = TODAY()+1;"Tomorrow")));
IF(S_info_3days[Correct_delivery_date] = TODAY()-1;"Yesterday";IF(S_info_3days[Correct_delivery_date] = TODAY();"Today";IF(S_info_3days[Correct_delivery_date] = TODAY()+1;"Tomorrow"))))
 
Yesterday = CALCULATE(COUNT('table'[column]);'table'[column] = "example filter1";IF(WEEKDAY(TODAY();1) = 2;'table'[Date] = TODAY() -3; 'Table'[Date] = TODAY() -1))
 
the 2nd solution you can use in a matrix for your values so lets say you have 4 columns in a matrix then you need to create it 4 times and change the attribute names, so its not that dynamic as i would like but it is a solution.
 
 

View solution in original post

4 REPLIES 4
dax
Community Support
Community Support

Hi Johnlogistic, 

I am not clear  about your requirement, I want to check the  requirement with you. Did you mean when you choose yesterday in slicer, it should show corresponding yesterday in visual, and when today is Monday, you need to show last Friday in visual? If this is not what you want, please correct me and inform me more details(such as your sample data and your expected output), then I will help you more correctly.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

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

Anonymous
Not applicable

Hi @dax 

I managed to solve the problem myself with research and lots of trial and error but now I encountered a different issue which i also created sample data for and is quite clear I think maybe easier to solve: https://community.powerbi.com/t5/Desktop/Filter-in-measure-on-date-time-but-column-has-multiple-time...

 

 

Anonymous
Not applicable

hi @dax 

Ok so I found a solution for my context well for category 2. I adapted the formula in the following way:

 
Days = IF(WEEKDAY(TODAY();1) = 2;
IF(S_info_3days[Correct_delivery_date] = TODAY()-3;"Yesterday";IF(S_info_3days[Correct_delivery_date] = TODAY();"Today";IF(S_info_3days[Correct_delivery_date] = TODAY()+1;"Tomorrow")));
IF(S_info_3days[Correct_delivery_date] = TODAY()-1;"Yesterday";IF(S_info_3days[Correct_delivery_date] = TODAY();"Today";IF(S_info_3days[Correct_delivery_date] = TODAY()+1;"Tomorrow"))))
 
Yesterday = CALCULATE(COUNT('table'[column]);'table'[column] = "example filter1";IF(WEEKDAY(TODAY();1) = 2;'table'[Date] = TODAY() -3; 'Table'[Date] = TODAY() -1))
 
the 2nd solution you can use in a matrix for your values so lets say you have 4 columns in a matrix then you need to create it 4 times and change the attribute names, so its not that dynamic as i would like but it is a solution.
 
 
Anonymous
Not applicable

Hi @dax 

So to give a little more context. I have multiple pbix files lets say 2 categories.

 category 1 uses the relative date filter option on a visual level filter of the delivery date with each " tab" having 3 diagrams. 1.last month. 2. last week 3. yesterday. Again here for the yesterday diagram if its Monday this diagram is empty because there is no data for sunday so i want it to display data of last friday instead. Wether or not this is possible using a visual level filter or if i would need to use a DAX formula i'm not sure.

 category 2 uses a calculated column to attach a day to a row in my query table (the formula written in my first post). This attribute which i called days is then used as the row value in a matrix so the rows would be today tomorrow and yesterday with some values attached to it. Here is the same problem. If its monday yesterday is empty due to the formula. Here again I would like it to display data of friday.

I cannot really provide data as It is very sensitive but I can however give screenshots of the empty diagrams so you can get an idea.

 

So to answer your question. Yes it should always show the date of yesterday except on monday then it should display last friday basically " skipping" the weekend. Not sure if different solutions are neccesary for both category. Pretty sure If a formula is made I can implement in both categories so to speak. If a sample data of pbix is neccesary I will completely strip it from data and make my own nonsense data for testing, but only if the formula isn't known having this information.

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.