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'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
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
@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
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...
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
Help when you know. Ask when you don't!
@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
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 |
---|---|
112 | |
108 | |
101 | |
93 | |
69 |
User | Count |
---|---|
173 | |
135 | |
132 | |
101 | |
95 |