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,910 points
1,264 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,890 points

Related Questions In Power BI

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,119 views
0 votes
1 answer

How do I get my DAX measure to calculate grouped values?

Try this: Total Usage:= SUMX( VALUES(MyTable[SensorID]), [Usage]) An alternative ...READ MORE

answered Oct 8, 2020 in Power BI by Gitika
• 65,890 points
671 views
0 votes
1 answer
0 votes
1 answer

Power BI DAX - adding date filter to DAX Query

VALUES('Combination'[Requirement]) gives you a table with all the ...READ MORE

answered Feb 23, 2022 in Power BI by CoolCoder
• 4,420 points
1,850 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,890 points
566 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,890 points
1,795 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,890 points
1,974 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,890 points
2,858 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
159,379 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