Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi,
I am trying to calculate the sales same time last year filtered by about 20 columns. What is the best way to do this? I been using caculate sum, sameperiodlastyear, and allexcept (with the 20 columns in the allexcept), but the calculation came out incorrect. I thought perhaps of duplicating the table and then instead of allexcept use filters, but I am sure there is an easier way.
Note: unfortunately due to data privacy I can't show the formula or data. The formula I wrote is something like
Solved! Go to Solution.
@Anonymous , Find the file again
if you need more help make me @
Appreciate your Kudos.
Why you need multiple allexcept
For multiple allexcept refer : https://community.powerbi.com/t5/Desktop/ALLEXCEPT-in-a-multitable-setup/td-p/403205
For YTD and LYTD you can use following with date calendar
YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31"))
This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD((ENDOFYEAR('Date'[Date])),"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
@amitchandak I am having problems replicating that.
How would you do it with the made-up data posted?
Please make up data that is not your actual data that emulates the problem. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
@Greg_Deckler below is the emulated data. I need to get the sales for the same time last year filtered by the different columns (i.e. product, currency, country, etc.)
Thank you so much for your help!
Country | Product | Currency Purchase | Month | Sales |
USA | A | USD | 1/1/2020 | 3000 |
USA | A | USD | 1/1/2019 | 2000 |
France | B | EUR | 1/1/2020 | 1000 |
France | B | EUR | 1/1/2019 | 500 |
USA | B | EUR | 1/1/2020 | 1500 |
USA | B | EUR | 1/1/2019 | 4000 |
France | C | EUR | 1/1/2020 | 750 |
France | C | EUR | 1/1/2019 | 200 |
@Anonymous , Please see if attached file can help
@amitchandak thank you for your response and work.
If you had to create another column in the table called Sales LY rather than measure would you do it differently?
@Anonymous , Find the file again
if you need more help make me @
Appreciate your Kudos.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
116 | |
108 | |
108 | |
93 | |
70 |
User | Count |
---|---|
173 | |
135 | |
131 | |
96 | |
94 |