Forum Discussion
Previous year value based on year integer slicer
Hello PBI Community,
I am having a hard time trying to figure out a solution for a seemilngly simple case.
What I have are two tables, Calendar and Sales. Calendar contains only one column which has unique integer numbers which represent years. The table Sales contains two columns, one is Periods which has multiple integer numbers for years, and Revenue which contains the revenue for the year.
Calendar:
| Year |
2024 |
2025 |
Sales:
| Period | Revenue |
| 2024 | 100 |
| 2024 | 100 |
| 2025 | 200 |
| 2025 | 200 |
The two tables are connected via the Year to Period relationship which is a 1:*.
What I am trying to achieve is to have a slicer for the Year from the Calendar table on the page and when I select a specific year, for another visual to show me the previous year values for the Revenue from Sales.
For example, if I select 2025 on the slicer, I should get back 200 based on the tables I have shown above.
I am currently using this formula:
PreviousYearValue =
VAR SelectedYear = SELECTEDVALUE('Calendar'[Year])
VAR PreviousYear = SelectedYear - 1
RETURN
CALCULATE(
SUM('Sales'[Revenue]),
'Sales'[Year] = PreviousYear
)
The issue is that when I select a year, it gives me blank.
Any help would be apprecated! Thank you!
Hi SGBaringa - you are getting that issue because of the filter context applied on slicer calender entity.
can you check the below formaule:
PreviousYearRevenue =
VAR SelectedYear = SELECTEDVALUE('Calendar'[Year])
VAR PreviousYear = SelectedYear - 1
RETURN
CALCULATE(
SUM('Sales'[Revenue]),
'Sales'[Period] = PreviousYear,
ALL('Calendar')
)It works.
3 Replies
Hi SGBaringa - you are getting that issue because of the filter context applied on slicer calender entity.
can you check the below formaule:
PreviousYearRevenue =
VAR SelectedYear = SELECTEDVALUE('Calendar'[Year])
VAR PreviousYear = SelectedYear - 1
RETURN
CALCULATE(
SUM('Sales'[Revenue]),
'Sales'[Period] = PreviousYear,
ALL('Calendar')
)It works.
This worked! Thank you so much rajendraongole1 !
Hi, SGBaringa
Based on your information, I I reproduce your situation.
I found no problem with your DAX. When I create a one-to-many relationship, when I use your DAX expression, there are no values in the table view.
You need to untie the two so that the table view shows the values of the previous year, and when there is no previous year, the table view will still appear empty.
How to Get Your Question Answered Quickly
Best Regards
Yongkang Hua
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
