Forum Discussion
Return Dates that Have No Value
The goal is to create a measure which will reflect all of the dates up to today regardless if there is a value for the date or not. My current measure correctly counts all of the transactions but the goal is to reflect any dates inbetween which may not have sales. Below you will find: Sample Data + Expected Output
Edit: I forgot to mention that I tried the following measure. It works by adding 0 to any dates in between which may not have sales but it will fail to stop adding 0 up till today's date. The below measure will continue to add 0's through the end of the year. Ideally, this measure will provide an output up till today's date.
IF(
[Product_Sales_Count] = BLANK(),
0,
[Product_Sales_Count]
)
Your support is greatly appreciated.
Sample Data
| Trans_ID | Product | Trans_Date |
| 1 | Pizza | 1-Sep-21 |
| 2 | Pizza | 3-Sep-21 |
| 3 | Tacos | 4-Sep-21 |
| 4 | Sushi | 7-Sep-21 |
| 5 | Sushi | 8-Sep-21 |
| 6 | Tacos | 9-Sep-21 |
Expected Output
| Product | Product_Sales_Count | Date |
| Pizza | 1 | 1-Sep-21 |
| 0 | 2-Sep-21 | |
| Pizza | 1 | 3-Sep-21 |
| Tacos | 1 | 4-Sep-21 |
| 0 | 5-Sep-21 | |
| 0 | 6-Sep-21 | |
| Sushi | 1 | 7-Sep-21 |
| Sushi | 1 | 8-Sep-21 |
| Tacos | 1 | 9-Sep-21 |
parry2k thank you for your support. I do have a Calendar/Date Dimension Table which I am using from SQLBI. This table works best for me because it has all of the fiscal references needed for my situation. I tried going through this long script to find the very last date reference ("LastDayCalendar" on Line #591) but my attempt at updating it resulted in breaking the model.
Any advice on how I can update this to have it reference today as the last date? Once again, so much gratitude for your support and advice.
4 Replies
- parry2k
Super User
Anonymous it is recommended to add a date dimension in your model, which you can easily create following my post here Create a basic Date table in your data model for Time Intelligence calculations | PeryTUS IT Solutions
After the table is added, set the relationship with the transaction table and then use the date from this new dimension table and your measure, and it should work.
✨ Follow us on LinkedIn
Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
parry2k thank you for your support. I do have a Calendar/Date Dimension Table which I am using from SQLBI. This table works best for me because it has all of the fiscal references needed for my situation. I tried going through this long script to find the very last date reference ("LastDayCalendar" on Line #591) but my attempt at updating it resulted in breaking the model.
Any advice on how I can update this to have it reference today as the last date? Once again, so much gratitude for your support and advice.
- parry2k
Anonymous sorry I have no idea what that it is. Not sure if I can assist with that.
