PDA

View Full Version : DAX formula to distribute Revenue over months



gdecome
2018-03-20, 03:31 AM
I trying to distribute revenue over months in a pivot table with the following scenario



Calendar Table included in the Data Model (not linked to any table)
Fact Table with the following Layout




Opp_ID

Sign_Date

Length

Contract End

TCV

Rev/Month



aaaaa

2017-01-15

3

2017-04-15

$90.00

$30.00



bbbbb

2017-02-20

4

2017-06-20

$80.00

$20.00



ccccc

2017-03-10

5

2017-18-10

$50.00

$10.00





First I tried the formula below that is working fine for months but not showing the right value for Quarter.
It is always the monthly value even in the row total.

RevDistribution:=CALCULATE( SUM ( [Rev/Month] ),
FILTER (Data, Data[Sign_Date] <= MAX(Calendar[Date]) &&
Data[Contract End] >= MIN(Calendar[Date]) ) )



Opp_ID

Month 01

Month 02

Month 03

Quarter 01



aaaaa

$30.00

$30.00

$30.00

$30.00



bbbbb



$20.00

$20.00

$20.00



ccccc





$10.00

$10.00





With the formula below months are still working but Quarter is showing the total value (3 months) even if months have no value.

RevDistribution2:=SUMX(VALUES(Calendar[Fiscal M]),
CALCULATE( SUM ( [Rev/Month] ),
FILTER (Data, Data[Sign_Date+1] <= MAX(Calendar[Date]) &&
Data[Contract End] >= MIN(Calendar[Date]) ) ) )




Opp_ID

Month 01

Month 02

Month 03

Quarter 01



aaaaa

$30.00

$30.00

$30.00

$90.00



bbbbb



$20.00

$20.00

$60.00



ccccc





$10.00

$30.00




Any help?