Forum Discussion

karimkz's avatar
karimkz
Advocate I
9 years ago

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...
Need Help
Tips and Tricks
karimkz's avatar
karimkz
Advocate I
9 years ago

Thank you for your answer! 

 

I see your point, I believe it will work out. Gotta learn how to do all that things though. 

 

Also I think of creating a date table, then do something like:

CALCULATE(SUM(performance); FILTER(date; date.month=EARLIEST(month))).

 

Do you think it will work?

Surendra_thota's avatar
Surendra_thota
Helper II
9 years ago

Hi 

 

we also have the same situation , but our tables are very huge so cant make it duplicate , is ithere any way for this issue , did you got the solution

  • karimkz's avatar
    karimkz
    Advocate I
    9 years ago

    Creating a calendar table and merging it with the existing table is still a best option. 

     

    • OpenDataLab's avatar
      OpenDataLab
      Helper II
      9 years ago

      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.  

       

    • maclura's avatar
      maclura
      Resolver I
      4 years ago

      Thanks karimkz you opened my mind!