I trying to distribute revenue over months in a pivot table with the following scenario
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]) ) )
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]) ) ) )
Any help?
- 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?