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,
I am building a dashboard where I am required to build a table with day by day sales and add another column to represent the Sales $ for Last Year. Se attached for what the table will look like -
I get my 'Net Revenue Actual' from my database but I am trying to figure out how I can add a column to calculate the Net Revenue for previous year for each date.
This is what my calendar table looks like -
Solved! Go to Solution.
Hi @aksharacoomar ,
Please refer to the similar thread:
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @aksharacoomar ,
Please refer to the similar thread:
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@aksharacoomar 4-5-4 should be 364 days behind.
So with help from the date table, you can try like the example
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-364,Day))
In case of Week or any other period, if you can create Rank, that can give period last period and last year same period
Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8
This Example of QTR, but true for all periods
Qtr Year = year([Date]) & [Qtr]
Qtr Rank = RANKX(all('Date'),'Date'[Qtr Year ],,ASC,Dense)
Qtr Start Date = minx(filter('Date', [Qtr Year] = earlier([Qtr Year])),[Date])
Qtr Day = DATEDIFF('Date'[Qtr Start Date],'Date'[Date],Day)+1
measure
This Qtr = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank])))
Last Qtr = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank])-1))
This QTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank]) && [Qtr Day] <=max([Qtr Day])))
Last QTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank])-1 && [Qtr Day] <=max([Qtr Day])))
@aksharacoomar You need a DimDate table: https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html
Then just use:
Net Revenue = SUM[Database[NetRevenueActual])
PY Net Revenue = CALCULATE([NetRevenueActual], DATEADD(DimDate[Date], -12, month) )
Then use in your table from screenshot with columns from the DimDate table.
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
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 |
---|---|
117 | |
104 | |
77 | |
73 | |
50 |
User | Count |
---|---|
145 | |
109 | |
108 | |
90 | |
64 |