Add missing days using PQ

Power BI
1

Hi everyone,

I want to add the comparison of 2 categories. However, sometimes we do not have continuous days where users purchase products. In the below picture, since we do not have any records on 1st & 2nd of July, our cumulative line break. How can I resolve this issue and bring records of missing days with 0 value? The Pbix file is attached.

image
image740×397 30.3 KB

image
image1181×675 34.6 KB

Missing days.pbix (50.9 KB)

Kind Regards,
Naila

2

@Naila,

It’s often a best practice in Power BI to add a date dimension to your model, especially when dealing with time-based analyses like cumulative calculations over time. The date dimension ensures you’ll have a continuous date range, even when your data do not include records for every single date. This will solve your issue of “gaps” in your cumulative line.

Take a look at eDNA Extended Date Table.

1 Like
3

Hi @HufferD ,

Thank you for your response. It makes sense to bring dates from the date table. However, this requirement is very specific. Category-A starts at a different date and Category-B starts at a different date, so day-1, day-2 etc. are different for each category. I am not sure if this can be achieved using a date table.

Kind Regards,
Naila

4

If you really need to create the missing rows in your data table, a Dates table can still help you accomplish this. I would suggest the following steps in Power Query.

  1. Make sure you have a Dates table that covers the time period you need.
  2. Open your data table.
  3. Select “Merge Queries” and select the Dates table as the second query.
  4. Select the “Ticket Assigned Date” column from the Data table and the “Date” column from the “Dates” table.
  5. Select “Right Outer Join” as the join type.
  6. Execute the join.
  7. Expand the newly created column and select the “Date” column as the column to expand.

After performing these steps, you will have rows for all dates including those that did not exist in the original Data table. After that, do whatever additional transformations you need to fill in the gaps in the new rows.

1 Like
5

Even if Category-A and Category-B start on different dates, a Date dim will ensure continuity in the date sequence. It’ll aslo keep a consistent structure for any date-related calculations and let you easily filter/slice data by time periods.

The way to handle different start dates for categories is to create a “Relative Day Index” for each category, starting from Day 1 based on the minimum date for each category in your data. You can then modify your measures to compute cumulative values based on this relative index rather than the actual date.

2 Likes
6

Hi @DaveC , @HufferD ,

Thank you so much for providing the solution. It worked for me.

Kind Regards,
Naila