Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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" :
Solved! Go to Solution.
hi @dax
Ok so I found a solution for my context well for category 2. I adapted the formula in the following way:
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.
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...
hi @dax
Ok so I found a solution for my context well for category 2. I adapted the formula in the following way:
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |