CODEWITHSUNDEEP
business-intelligencepowerpivotdaxpowerbi
Rafael
Rafael

Reputation: 1048

Powerpivot: Subtract first member of group

Consider the following table:

Group ID    Date
1001        2014-01-05
1001        2014-01-12
1001        2014-01-22
1002        2014-01-15
1002        2014-01-19

How may I calculate the Duration inside a group in days? Duration is defined as Date[k] - Date[0]

e.g.: The desired table should look like:

Group ID    Date          Duration
1001        2014-01-05    0  
1001        2014-01-12    7
1001        2014-01-22    17
1002        2014-01-15    0
1002        2014-01-19    4

I have tried using MINX to return the group's first Date value:

=MINX(FILTER(Table;Table[Group ID] = Table[Group ID]); Table[Date])

But the value returned is the entire table's minimum, as if FILTER is returning the entire table.

Upvotes: 0

Views: 283

Answers (2)

Jacob
Jacob

Reputation: 3587

It's not clear whether you are trying to produce a Calculated Column or a Measure - you should probably be trying the later but it can be done either way.

The calculated column would simply be:

= [Date] - CALCULATE(MIN(Table[Date]), FILTER(Table, Table[Group_ID] = EARLIER(Table[Group_ID])))

Far more powerful are Measures! You could get this date 

[Raw Measure] = MAX(Table[Date]) - CALCULATE(MIN(Table[Date]), ALL(Table[Date]))

This works because the ALL() opens up the filter context on the Date column whilst retaining the filter on the group.

You would need to iterate over this measure as it would object to the total, in this example you could use:

[Iterated Measure] = SUMX(Table, [Raw Measure])

Upvotes: 1

user3796279
user3796279

Reputation: 26

May depend on what you need to do, but...

=MAX(Table[Date]) - MIN(Table[Date]) could theoretically work w/ GroupID on rows?

Upvotes: 0

Related Questions