Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi ,
I need to calculate sales for current quarter vs last quarter . Problem is it has to be calculated for the same day as today for last quarter.
For example : if today is 9-19-2018 Q3 then it should show sum of sales for 6-19-2018 Q2.
Similarly i need to do that for Last Year .
Any idea how this can be done ?
Thanks
Not knowing your data model its difficult give an accurate solution. hopefully below can nudge you in right direction
depending on your data model create measures
Sum({<Date="$(=today())">}
Sum({<Date="$(=AddMonths(today(),-3))">}
Sum({<Date="$(=AddYear(today(),-1))">}
Sum({<Date="$(=AddYear(AddMonths(today(),-3),-1))">}
Maybe create flags for previous year and previous quarter in back end first
if(InYear(TempDate, today(),-1), 1, 0) as LastYearFlag,
if(InQuarter(TempDate, today(),-1), 1, 0) as LastQtrFlag,
and in front end use
Previous Quarter:
sum({< Week = {">=$(=Max(Week(Date)))"}, LastQtrFlag ={1} >} YOURMEASUREHERE )
Previous Year:
sum({< Week = {">=$(=Max(Week(Date)))"}, LastYearFlag={1} >} YOURMEASUREHERE )
Not sure if this would work but you need to have Week, Date, Year, Month and Quarter field (derive it in Master Calendar script or if using only fact table define in fact)