Forum Discussion
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:
| Day | Material | Qty |
| 01.09.2022 | A | 10 |
| 01.09.2022 | B | 100 |
| 01.09.2022 | C | 1000 |
| 08.09.2022 | A | 20 |
| 08.09.2022 | B | 200 |
| 08.09.2022 | D | 20000 |
| 15.09.2022 | A | 30 |
| 15.09.2022 | B | 3000 |
| 15.09.2022 | C | 3000 |
| 15.09.2022 | D | 30000 |
To:
Intended result (In Matrix table)
| Day/Qty | A | B | C | D |
| 01.09.2022 | 10 | 100 | 1000 | 0 |
| 02.09.2022 | 10 | 100 | 1000 | 0 |
| 03.09.2022 | 10 | 100 | 1000 | 0 |
| 04.09.2022 | 10 | 100 | 1000 | 0 |
| 05.09.2022 | 10 | 100 | 1000 | 0 |
| 06.09.2022 | 10 | 100 | 1000 | 0 |
| 07.09.2022 | 10 | 100 | 1000 | 0 |
| 08.09.2022 | 20 | 200 | 1000 | 20000 |
| 09.09.2022 | 20 | 200 | 1000 | 20000 |
| 10.09.2022 | 20 | 200 | 1000 | 20000 |
| 11.09.2022 | 20 | 200 | 1000 | 20000 |
| 12.09.2022 | 20 | 200 | 1000 | 20000 |
| 13.09.2022 | 20 | 200 | 1000 | 20000 |
| 14.09.2022 | 20 | 200 | 1000 | 20000 |
| 15.09.2022 | 30 | 300 | 3000 | 300000 |
| 16.09.2022 | 30 | 300 | 3000 | 300000 |
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
HCK , First create a new column
End Date = coalesce(Minx(filter(Table, [Material] = earlier([Material]) && [Date] > earlier([Date])), [Date]), [Date]+1)
then use table of measure way in dax and use a matrix visual
Table
https://amitchandak.medium.com/dax-get-all-dates-between-the-start-and-end-date-8f3dac4ff90b
amitchandak Thank you very much. You showed me the way.
I needed to adjust the new column a bit so that it works exactly as intended to:EndDate = coalesce(Minx(filter(Data, [Material] = earlier([Material]) && [StartDate] > earlier([StartDate])),[StartDate]-1),[StartDate]+1)
I adjusted as well the following measure differently (simplified):Val by day of Month = CALCULATE (SUMX(Filter(CROSSJOIN('Data','Date'),'Date'[Date] >= 'Data'[StartDate] && 'Date'[Date]<= 'Data'[EndDate]),Data[Qty]))
It leads to wanted results!
Thank you very much again!
2 Replies
- amitchandak
Super User
HCK , First create a new column
End Date = coalesce(Minx(filter(Table, [Material] = earlier([Material]) && [Date] > earlier([Date])), [Date]), [Date]+1)
then use table of measure way in dax and use a matrix visual
Table
https://amitchandak.medium.com/dax-get-all-dates-between-the-start-and-end-date-8f3dac4ff90b
- HCKFrequent Visitor
amitchandak Thank you very much. You showed me the way.
I needed to adjust the new column a bit so that it works exactly as intended to:EndDate = coalesce(Minx(filter(Data, [Material] = earlier([Material]) && [StartDate] > earlier([StartDate])),[StartDate]-1),[StartDate]+1)
I adjusted as well the following measure differently (simplified):Val by day of Month = CALCULATE (SUMX(Filter(CROSSJOIN('Data','Date'),'Date'[Date] >= 'Data'[StartDate] && 'Date'[Date]<= 'Data'[EndDate]),Data[Qty]))
It leads to wanted results!
Thank you very much again!