Value of previous year than time slicer

[Anonymous]

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

[amitchandak]

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

[Anonymous]

@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

[amitchandak]

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

[kentyler]

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.

[Anonymous]

@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