Hello,
Hoping someone can help me with this issue as I’m truly baffled. Thank you in advance.
Basically, what I am doing is building a financial tool that can explore trends for current month, year to date, rolling 12 months & rolling three months. I’ve calculated these with the normal trend equation, but have to add an additional calculation to adjust for loss/gain of a selling day. So my trend equation ends up being: this years sales/ last years sales -1 +adjustment for selling day/100
I imported a table that has selling day broken down for each time period and used RELATED to add them as columns to my data table. So really all I have to do pull in matching value from my selling day adjust column.
Here’s the issue:
For my Current month and year to date equations, this works perfectly. For the equations where I am using DATEADD or DATESBETWEEN, nothing happens. It doesn’t even register that part of the equation.
I have a filter on for April 2019, I just want the selling day to pull for the matching line for April 2019.
Here’s an example of my current month measure that works:
CM Trend = (Data_Vol[cm])/(Data_Vol[PY CM Vol])-1+AVERAGE(Data_Vol[SD Month])/100
These are the measures that create [cm] and [PY CM Vol]:
CM = CALCULATE(sum(Data_Vol[VOL_CASES]))
PY CM Vol = CALCULATE(Data_Vol[total sales],DATEADD('Date'[Date],-12,MONTH))
This works just fine.
Here is an example of the measure that does not work:
PM Trend = (Data_Vol[CY PM])/(Data_Vol[PY PM])-1+AVERAGE(Data_Vol[SD PM)/100
Here are the measures that create: [CY PM] and [PY PM]:
CY PM= CALCULATE(Data_Vol[totalsales], DATEADD('Date'[Date],-1,MONTH))
PY PM = CALCULATE(Data_Vol[totalsales], DATEADD('Date'[Date],-13,MONTH))
Here are the measures for the rolling 3months that are also not equating. the trend calculation is essentially the same.
PY L3M = CALCULATE(Data_Vol[total sales],DATESBETWEEN('Date'[Date],FIRSTDATE(DATEADD('Date'[Date],-14,month)),LASTDATE(DATEADD('Date'[Date],-12,MONTH))))
CY L3M = CALCULATE(Data_Vol[total sales], DATESBETWEEN('Date'[Date],FIRSTDATE(DATEADD('Date'[Date],-2,month)),LASTDATE(DATEADD('Date'[Date],0,MONTH))))
Very confused as to why some measures are calculating correctly and some are not. Any help would be most appreciated.
Thanks
