Forum Discussion

M_SBS_6's avatar
M_SBS_6
Helper V
3 years ago

Month on Month Variance

Hi, 

 

I have written out the followinng DAX to help me create a month on month variance within my line and clustered column chart. 

month on month sales variance =
var lastmonth = CALCULATE(SUM(fact_app[cost]), DATEADD(fact_app[app date], -1, MONTH))
var thismonth = CALCULATE(SUM(fact_app[cost]), DATEADD(fact_app[appdate], 0, MONTH))
return
IF(lastmonth = BLANK(), BLANK(), IF(thismonth=BLANK(), BLANK(), (thismonth/lastmonth)-1))

I get the following error message: 
"A date column containing duplicate date was specificed in the call to funtion 'DATEADD'. This is not supported'. 

Amy idea how to resolve please? 

Need Help

1 Reply

  • Mkarwa-123's avatar
    Mkarwa-123
    Resolver II
    3 years ago

    Hello M_SBS_6 
    you need a date table without duplicate dates for time intelligence functions.

    Did I answer your question? Mark my post as a solution!