CODEWITHSUNDEEP
powerbi
Felix
Felix

Reputation: 13

Add missing month rows based on multiple columns with multiple variables in Power BI

I'm very stuck and I was hoping you can help. So have the following dataset (Table 1) with Month (5 years worth), Customer (1000 customers), Product (100 products), Units and Value (value is just unit multiple a price). The data only shows rows with unit and value, so for customers when there is no sale in a month, there is no data. Click here for Table 1

I want to create a table (Table 2) where every product for every customer is shown for all time periods, where actual units and values are included and those missing in Table 1 are now showing 0. Click here for Table 2

I have read many posts here and elsewhere, which only handles 1 column (e.g. only Customer not both Customer AND Product, and only 1 measure not Unit and Value). I tried to adapt the code but failed miserably.

I also want to do this in Power BI using M not DAX, because I would like to further transform the data.

Thank you so much everyone!!!

Upvotes: 1

Views: 412

Answers (1)

Ekaterina Da Silva
Ekaterina Da Silva

Reputation: 36

Good afternoon. You can use DAX to create a calendar for the required period of time. Use the minimum and maximum values from Table 1 for the interval (Calendar function).

Calendar = CALENDAR(Date(2022,5,1), TODAY() -1)

Link the calendar to the necessary dates.

calendar link example

In the settings of the table to which you will output data, select the "Show items without data" setting, and take the date from the calendar.

Upvotes: 2

Related Questions