DAX - Date calculations based on pivot filters

Question

I just learned about PowerPivot last week, so I'm still trying to wrap my head around how this all works.

I have a table of contracts with start and end dates. I have a second lookup with month (indices) and month end dates. (1) The DAX calculates the number of days in the contract. (2) Based on the Month filtered, it calculates the number of days between the contract start and the MonthDate.

Contracts:

Account   Start       End        ContractDays  DevDays
359ABC    1/10/2019   1/10/2020
948EDI    2/5/2019    2/5/2020

Lookup Table:

Month    MonthDate
1        1/31/2019
2        2/28/2019
3        3/31/2019

I want the DAX to calculate ContractDays and DevDays in the Contracts table above.

1. Calculate ContractDays

I think I got this one. I added a column called ContractDays with the formula =[End]-[Start]. Easy...

2. Calculate DevDays

If the user filters to Month = 2, then I want to calculate the number of days between the contract start and 2/28/2019. So the resulting pivot would show:

Account   Start       End        ContractDays  DevDays
359ABC    1/10/2019   1/10/2020  365           49
948EDI    2/5/2019    2/5/2020   365           0

I'm calculating a measure called UseEvalDate = lookupvalue(lku[MonthDate], lku[Month], filters(lku[Month])). This determines the MonthDate to use.

Then I added a column in Contracts: DevDays = [UseEvalDate] - [Start]. This is erroring out... saying "A table of multiple values was supplied where a single value was expected." What am I missing?

waealu waealu · Accepted Answer · 2019-08-26T13:51:01

I found the trick is to use measures for all of these things. My final measure for the number of days between the MonthDay and Contract start is: DevDays = sumx(contract, datediff(contract[Start], [UseEvalDate], day)).

DAX - Date calculations based on pivot filters

1 Answers