Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors