Rollup instrument readings

[jjr333]

Hi, my measure below returns expected results on the maxtrix below, when drilled down to day level.
However if I drill to month and/or year the values for 'kWhPerDay' are not summed.
Any ideas?

Cheers,
Julian

kWhPerDay = 
    VAR _DATESTART = MIN(dimDate[Date])
    VAR _DATEEND = MAX(dimDate[Date])
    RETURN
    CALCULATE(
        SUM(LuzSanSeb[kWhPerDay]), 
        _DATESTART >= LuzSanSeb[DateStart] && 
        _DATEEND < LuzSanSeb[DateEnd]
            )

 

[daxer-almighty]

OK, one sensible way to define it is this:

1) For any selected period of time find the rows in the fact table that have a non-empty intersection with it in terms of time, of course.

2) Multiply the daily average for every row by the number of days which are the members of the intersection.

3) Sum these multiplications up over the rows and divide this total by the total number of days in the intersection, which would be the number of days in the month/year IF you can guarantee that for each day in the selected period of time there is a row in the fact table such that its intersection with the period is non-empty. Otherwise, you'll have to be more careful and count the number of days that do belong to the intersection.

That would be the average.

[daxer-almighty]

According to this logic:

        _DATESTART >= LuzSanSeb[DateStart] && 
        _DATEEND < LuzSanSeb[DateEnd]

it's absolutely correct what you see.

You don't get any number for months and years because there is no row in the fact table that satisfies the condition above when these pieces of time are selected. They are just too wide for any row to qualify.

[jjr333]

But how do I sum the data when drilling up to Month or Year?
TIA, Julian

[daxer-almighty]

That depends on how you want to do it. There is no absolutely one right way (but there are many bad and incorrect ways). You need to define what you want to see in cases the time span is such that no single row qualifies.