Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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]))
Solved! Go to Solution.
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
Hi all!
I'm trying to get the sales value of the previous previous month but i'm not been able to reach it using parallelperiod formula.
Now we are working in March, so I must get January value.
Thanks
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |