DAX formula to distribute Revenue over months

gdecome

New member
Joined
Feb 20, 2018
Messages
1
Reaction score
0
Points
0
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?
 
Top