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?