Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
Anonymous
Not applicable

Value of previous year than time slicer

Hi,

I'm trying to create a matrix with Spend Current year and Spend Last year (by Account). 

Before I wasn't using a (date) slice on the view and my variable was defined like this 

    Actual spent LYear = IF ((YEAR(TODAY())-1)= Expense_Forecast_Budget_2019[MergeDate].         [Year],Expense_Forecast_Budget_2019[Actual Spend], 0)

However now that I have a time(year) slicer this doesn't work anymore. I tried a couple of different solutions, none worked. 

I tried:

lastYearsalesAmount =
CALCULATE (
    SUM ( Yearbysale[Sales] ),
    FILTER (
        ALLSELECTED ( Yearbysale ), 
        Yearbysale[Year]
            = MAX ( Yearbysale[Year] ) - 1
    )
)

In this case I am not exacly sure how what is YearbtSale (the table's name?)
and

Total Sales Last Year = CALCULATE([ActualSpend], SAMEPERIODLASTYEAR(Expense_Forecast_budget[MergeDate])) 
 
 


Thanks for helping me,
PS: I'm a newbie on Power Bi

5 REPLIES 5
amitchandak
Super User
Super User

It is better to have a date calendar.  After that you can use formulas like given below.

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"3/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))
2 Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(sales,sales[date] <=maxx(date,date[date])))

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-12,MONTH))  
Rolling last 12 before 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(dateadd('Date'[Date],-12,MONTH)),-12,MONTH))  

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/

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here
Anonymous
Not applicable

@amitchandak  Thank you for you answer.
I created a data as describe in your process. But this data is included in one of my table. I am not using a Star Schema. 
( I created two views in SQL. Then I do a merge in SQL (for having a full join). Then I created new columns which rename the same the variables. (basically the first view represents the spenditure and the second view a target spenditure).

When I try to create 

Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))

 Which is by me

Last YTD Sales = CALCULATE(SUM(Expense_Forecast_Budget_2019[Actual Spend]),DATESYTD(dateadd(Expense_Forecast_Budget_2019[Calendar],-1,Year),"12/31"))

It gives no value in my Matrix. Why? 
Thank you

Hope date table is marked as date , try adding all(calendar). If possible please share a sample pbix file after removing sensitive information.

 

Last YTD Sales = CALCULATE(SUM(Expense_Forecast_Budget_2019[Actual Spend]),all(Expense_Forecast_Budget_2019[Calendar]),DATESYTD(dateadd(Expense_Forecast_Budget_2019[Calendar],-1,Year),"12/31")).

My Recent Blog -

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here
kentyler
Solution Sage
Solution Sage

The basic trick is to store the year selected in the slicer in a VAR and then use that in your expression, rather than trying to get the slicer value inside the calculate

 

VAR cur_year = YearbySales[Year]  // or whatever the column is you're using in the slicer

Actual spent LYear = IF (cur_year= Expense_Forecast_Budget_2019[MergeDate], [Year],Expense_Forecast_Budget_2019[Actual Spend], 0) 

I'm a personal Power Bi Trainer I learn something every time I answer a question

The Golden Rules for Power BI

  1. Use a Calendar table. A custom Date tables is preferable to using the automatic date/time handling capabilities of Power BI. https://www.youtube.com/watch?v=FxiAYGbCfAQ
  2. Build your data model as a Star Schema. Creating a star schema in Power BI is the best practice to improve performance and more importantly, to ensure accurate results! https://www.youtube.com/watch?v=1Kilya6aUQw
  3. Use a small set up sample data when developing. When building your measures and calculated columns always use a small amount of sample data so that it will be easier to confirm that you are getting the right numbers.
  4. Store all your intermediate calculations in VARs when you’re writing measures. You can return these intermediate VARs instead of your final result  to check on your steps along the way.







Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!




Join the conversation at We Talk BI find out more about me at Slow BI


Anonymous
Not applicable

@kentyler  Thank you for your answer. A couple of questions/ remarks:
- Should I keep a slicer? If yes, I don't see where in the the process you describe Actual Spent LYear is going to show the value of the year before. When I follow your proccess, it shows value in the year choosen in the slicer.
- If I don't have a slicer, how can the end user select different year and have the values? Thank you

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.