Hi,
In the report I am creating, I have 15 minutes data across different units.
There is a time dimension data goes from the 15 minutes to the hour and then to the day -> month -> year.
There is an asset hierarchy that goes from a unit to a site -> region -> country and so on.
The fact table reporting data is linked from one side to the 15 minute intervals table and on the other side to the unit table.
Not all the units started to log data at the same time.
I want to calculate how many samples are expected from a unit since when it started logging to today. This numeber may vary if I restrict the date range, to its min and max date.
This is the Measure I created, which performs the calculation as I expect:
Unit Hours =
VAR DateFrom = MAX(MIN('Date'[Date]), MIN('Unit'[Data available from]))
VAR DateTo = MIN(TODAY(), MAX('Date'[Date]))
VAR ExpectedHours = CALCULATE(SUM('Date'[Hours in the day]), FILTER('Date', 'Date'[Date] >= DateFrom), FILTER('Date', 'Date'[Date] <= DateTo))
RETURN ExpectedHours
In the measure I used the sum of hours in the day since I need to take into account daylight saving, so the days in the time range can have 23, 24 or 25 hours, according to the selection.
If I put it in a table which shows the list of units the value are the ones expected and there is one row per unit.
If I add to this table site, region and country fields for the unit, or other fields from the asset dimension, the row numbers increase.
How can I prevent that?
Thank you
