Derive a Monthly Amount column from a Running Sum Total column

DAX DAX Calculations
1

Hi,

I’ve been able to join a date column to a date table where numeric values of Month and Year exist, and the comparable DAX measure logic I’m using will work. However, I’d like to get the “_MonthlyRevenue” measure to work using the fact table “period start date” value.

In the attached sample csv source file and pbix, the ATD_RVNU column reflects a cumulative total [for the project & group] by period start date (i.e. first date of each month). I’m looking to work backwards from the running sum total value by period start date, and derive a monthly revenue column (a.k.a. “_MonthlyRevenue”).

Is there DAX which would produce the monthly revenue amount without joining a date table?
Please modify/share the DAX measure logic which does the trick.

Thank you,
Kevin

Derive the monthly amount from a running sum total.csv (13.3 KB)
EDNA - Derive Monthly Amount from Running Sum Total.pbix (50.1 KB)

2

Any thoughts on this item?

3

Hi,

The first thing i notice in your provided PBIX that your PROJ_NBR(project number) in table is aggregated because it is numeric if i interpret it right then it should not be agg so i remove that agg and done the below changes in your existing DAX:

_MonthlyRevenue =
VAR MonthCurrent = month(SELECTEDVALUE(‘Cumulative Revenue’[Period Start Date]))
VAR YearCurrent = year(SELECTEDVALUE(‘Cumulative Revenue’[Period Start Date]))
VAR proj=SELECTEDVALUE(‘Cumulative Revenue’[PROJ_NBR])
RETURN

IF ( MonthCurrent - 1 > 0,
CALCULATE(
SUM( ‘Cumulative Revenue’[ATD_RVNU])
- CALCULATE(
SUM(‘Cumulative Revenue’[ATD_RVNU] ),
FILTER( ALL(‘Cumulative Revenue’), ‘Cumulative Revenue’[Period Start Date].[Year] = YearCurrent && ‘Cumulative Revenue’[Period Start Date].[MonthNo] = MonthCurrent - 1 && ‘Cumulative Revenue’[PROJ_NBR]=proj)
)
),
CALCULATE(
SUM(‘Cumulative Revenue’[ATD_RVNU])
- CALCULATE(
SUM(‘Cumulative Revenue’[ATD_RVNU]),
FILTER( ALL(‘Cumulative Revenue’), ‘Cumulative Revenue’[Period Start Date].[Year] = YearCurrent - 1 && ‘Cumulative Revenue’[Period Start Date].[MonthNo] = 12 && ‘Cumulative Revenue’[PROJ_NBR]=proj)
)
))

Attaching the PBIX for your refrence :
EDNA - Derive Monthly Amount from Running Sum Total_A.pbix (50.8 KB)

Pls let me know if this req result or you need something else.

Thanks,
Anurag