Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Not applicable

Value of previous year than time slicer


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 =
    SUM ( Yearbysale[Sales] ),
    FILTER (
        ALLSELECTED ( Yearbysale ), 
            = MAX ( Yearbysale[Year] ) - 1

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

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

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

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.


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

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 -

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.
  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!
  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

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

PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Dashboard in a day with date

Exclusive opportunity for Women!

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!

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors