cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jjr333
Advocate I
Advocate I

Rollup instrument readings

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]
            )

 

Matrices.gif 

4 REPLIES 4
daxer-almighty
Solution Sage
Solution Sage

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
Solution Sage
Solution Sage

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.

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

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.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.