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.
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 :
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!
@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?
Take a look at the September 2023 Power BI update to learn more.
Join Microsoft Reactor and learn from developers.
Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!
Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.