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 in Power BI by Rajiv
• 4,960 points
48 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 by Gitika
• 50,620 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
2,008 views
+3 votes
1 answer

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
• 32,490 points
40,442 views
0 votes
1 answer
0 votes
1 answer

Dax query in power bi

Here is my table before I have ...READ MORE

answered Oct 22, 2018 in Power BI by Hannah
• 18,410 points
242 views
0 votes
1 answer

Excel PowerPivot DAX Calculated Field

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

answered Oct 5 in Power BI by Gitika
• 50,620 points
28 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 in Power BI by Gitika
• 50,620 points
14 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 in Power BI by Gitika
• 50,620 points
36 views
0 votes
1 answer

DAX EARLIER() function in Power Query

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

answered Oct 1 in Power BI by Gitika
• 50,620 points
105 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 in Power BI by Gitika
• 50,620 points
32 views