Forum Discussion
Helper ILLY and LLLY DAX
Hello All,
i am trying to calculate total sales amount for each week,Month,Qtr.
I have calendar table and i have created a heirarchy Year,Season,Quarter,Month,Week( 2 seasons per year).
What i want to display in reports is total amount of sales by period for current Year, Previous Year(LY), previous Previous Year(LLY),Previous Previous Previous year(LLLY) ( by Heirarchy on Matrix table)
TSales =
CALCULATE(SUM('sales'[sales_amt]),'bi_calendar'[bi_date], bi_calendar[IsInCurrentYear]=1)
LLY Sales =
CALCULATE(SUM('sales'[sales_amt]),SAMEPERIODLASTYEAR(SAMEPERIODLASTYEAR('bcalendar'[bdate].[Date]))
You could use PARALLELPERIOD function which returns a table that contains a column of dates that represents a period parallel to the dates in the specified dates column, in the current context, with the dates shifted a number of intervals either forward in time or back in time.
Sample DAX.
=CALCULATE(SUM(InternetSales_USD[SalesAmount_USD]), PARALLELPERIOD(DateTime[DateKey],-2,year))
Regards,
Charlie Liao
2 Replies
v-caliao-msftMicrosoft Employee
You could use PARALLELPERIOD function which returns a table that contains a column of dates that represents a period parallel to the dates in the specified dates column, in the current context, with the dates shifted a number of intervals either forward in time or back in time.
Sample DAX.
=CALCULATE(SUM(InternetSales_USD[SalesAmount_USD]), PARALLELPERIOD(DateTime[DateKey],-2,year))
Regards,
Charlie Liao
