Forum Discussion
Missing dates with one criteria.
Created a solution with some dummy data similar to yours.
Does this work for you?
All dates (from 21st to 26th for all names)
You can download the excel file (power query solution) here
Hope it helps
Hi parry2k,
First of all thank you.
Here is the thing, I use the query as an input for a pivot table and forecast calculations. Missing dates are creating discontinuities in my graph when I sum up them all.
I created a macro and it was working well but since I use powerquery to update my datas I need something more convenient .
I know that in pivotable table you can replace missing datas with a value but the thing is that I need to have no discontinuities in the timeline for my forecasts.
I hope it's clear...
Super UserAnonymous as mentioned date table in your model is exactly helps you to achieve this without adding missing dates in your table.
Here are the steps:
- add Calendar table in your model (there are many posts on how to create/add Calendar table)
- join your transaction table with Calendar table
- use date from Calendar on x-axis
- choose option to show "show with no data"
if you run into any issue on above step, feel free to reach out.
Yep I tried this already but it seems to work when you have only one name for example. As I have many names powerBi has to understand that for each name no date is mssing (kind of loop I guess?)
- parry2k
Anonymous not sure what you mean by one name. Can you share more details and how you are doing it?
By name I mean the column "Name" in my example. If you have only name "A" then it works to merge this table with the date table. As I have name "A" and "B" and so on then the query has to cmpare all dates from names "A" & "B" to all dates in the dates table.