Forum Discussion

HCK's avatar
HCK
Frequent Visitor
4 years ago
Solved

Is there a way how to calculate and visualize data for missing dates using DAX

Hello,

as a relativelly new to DAX I am in front of for me relativelly big data which I assume would be better to handle on DAX lever rather than Power Query - matching qty for materials (8000+) for each day (365+) and that based on random dated input.
I have a Data table and a Calendar table (Day by Day) linked together with the key day.
Data(Day)*:1Calendar(Day)
I would like to transform the data from:

 

Data table:

DayMaterialQty
01.09.2022A10
01.09.2022B100
01.09.2022C1000
08.09.2022A20
08.09.2022B200
08.09.2022D20000
15.09.2022A30
15.09.2022B3000
15.09.2022C3000
15.09.2022D30000

 

To:

Intended result (In Matrix table) 

Day/QtyABCD
01.09.20221010010000
02.09.20221010010000
03.09.20221010010000
04.09.20221010010000
05.09.20221010010000
06.09.20221010010000
07.09.20221010010000
08.09.202220200100020000
09.09.202220200100020000
10.09.202220200100020000
11.09.202220200100020000
12.09.202220200100020000
13.09.202220200100020000
14.09.202220200100020000
15.09.2022303003000300000
16.09.2022303003000300000

 

The purpose is that the original data for saving purposes shows only data with change and I would like to reconstruct every day status.
I can imagine doing that on Power Query level however I assume that will be too big and don't think it will be the right approach.

 

I tried to do some measures to do the filling but it all seem to only reflect some dates and not all from the Calendar.

I will appreciate a lot an advice leading to the proper solution. 
Thank you very much for your help  in advance.

HCK

2 Replies