DAX PowerPivot query functions to spread aggregated values over time period

0 votes

How do I split/chunk the pre-summed value for long-running tasks to match the time interval I’m looking for?

Any ideas are greatly appreciated! I’m open to doing some additional ETL/data creation as a PowerQuery step, but would really like to figure out the right DAX expression for this so it can be something available as a time-slicer/filter on the project.

Oct 5, 2020 in Power BI by Rajiv
• 8,870 points
1,344 views

1 answer to this question.

0 votes

The solution below assumes a table called 'data' that has 4 columns: id, start, end, value, and table called calendar that has 2 columns Date and Month.

Measure 1: Sum the hours

[Hours] =SUM(Data[Value])

Measure 2: Apply the hours to the dates and divide by the number of dates

[Hours Apportioned Raw] =
 CALCULATE ([Hours],
FILTER (
    Data,
    Data[Start] <= MAX ( Calendar[Date] )
        && Data[END] >= MAX ( Calendar[Date] )
       )
            )
/ ( MAX ( Data[End] ) - MAX ( Data[Start] ) )

Measure 3: Iterate Measure 2 over dates and ids to give correct values

=
    SUMX (
        VALUES ( Calendar[Date] ),
        SUMX ( VALUES ( Data[ID] ), [Hours Apportioned RAW] )
    )

Hope this makes some sense.

answered Oct 5, 2020 by Gitika
• 65,770 points

Related Questions In Power BI

0 votes
0 answers

Why is my DAX measure displaying incorrect values when using time intelligence functions?

Why is my DAX measure displaying incorrect ...READ MORE

Nov 7 in Power BI by Evanjalin
• 7,080 points
36 views
0 votes
1 answer

Why is my DAX measure displaying incorrect values when using time intelligence functions?

Erroneous output is typical when using DAX ...READ MORE

answered Nov 12 in Power BI by pooja
• 6,670 points
55 views
0 votes
1 answer

To expand all lists in a row of lists at the same time without recurring values

Can't promise simple, but I have something ...READ MORE

answered Feb 19, 2019 in Power BI by Upasana
• 8,620 points
6,215 views
0 votes
1 answer

Excel PowerPivot DAX Calculated Field

You should create a relationship between each ...READ MORE

answered Oct 5, 2020 in Power BI by Gitika
• 65,770 points
600 views
0 votes
1 answer

PowerPivot DAX - Dynamic Ranking Per Group (Min Per Group)

Firstly create a basic measure [Amount] to sum the ...READ MORE

answered Nov 18, 2020 in Power BI by Gitika
• 65,770 points
1,832 views
0 votes
1 answer

What is the difference between PowerPivot, Power Query and Power BI?

Power Query Power Query is a self-service ETL ...READ MORE

answered Sep 24, 2020 in Power BI by Gitika
• 65,770 points
2,013 views
0 votes
1 answer

DAX EARLIER() function in Power Query

Solutions in the code below. Notice that ...READ MORE

answered Oct 1, 2020 in Power BI by Gitika
• 65,770 points
2,944 views
+2 votes
3 answers

How to replace null values with custom values in Power BI(power query editor)?

Hi Nithin, To fill or replace the null or ...READ MORE

answered Mar 8, 2019 in Power BI by Cherukuri
• 33,030 points
160,075 views
0 votes
1 answer
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP