Forum Discussion
Add rows / missing date values
Hi all. Recently I found out that my dataset from SQL server lacks some information. My dataset is about machine units and their daily performance. However, if a unit did not work some particular...
Creating a calendar table and merging it with the existing table is still a best option.
You need to dimensionalise your data. I would create a separte date table and a seperate unit table (with the distinct list of unit types). You can then use a calculations like this:
CALCULATE
(
AVERAGEX
(
CROSSJOIN
(
VALUES('Unit'[Unit]),
VALUES('Date'[Date])
)
,SUM([Performance])
)
)
CROSSJOIN will give you the product of all dates and all units that are currently being filtered. Then AVERAGEX will iterate over this calculating the SUM and then finally calculating the average.