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?