- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Find preivous year value for multiple columns
Hello Community,
New to powerbi and I have data as in table 1.
Table1:
Id | Year | Country | City | Sales | Profit | Difference |
1 | 2020 | India | Delhi | 100 | 50 | -50 |
2 | 2021 | India | Mumbai | 200 | 90 | -110 |
3 | 2022 | India | Delhi | 300 | 150 | -150 |
4 | 2020 | Nigeria | Abuja | 50 | 10 | -40 |
5 | 2021 | Nigeria | Lagos | 30 | 20 | -10 |
6 | 2022 | Nigeria | Abuja | 55 | 40 | -15 |
7 | 2020 | Zimbabwe | Harare | 60 | 20 | -40 |
8 | 2021 | Zimbabwe | Harare | 30 | 25 | -5 |
9 | 2022 | Zimbabwe | Harare | 50 | 19 | -31 |
Now, I have to create a chart/table visual where I have to show prior year data w.r.t City-Country (as measure) for each current year selection (as in table below).
Expected output:
Id | Year | Country | City | Sales | Profit | Difference | Prior year sales | Prior year profit | Prior year difference |
1 | 2020 | India | Delhi | 100 | 50 | -50 | 0 | 0 | 0 |
2 | 2021 | India | Mumbai | 200 | 90 | -110 | 0 | 0 | 0 |
3 | 2022 | India | Delhi | 300 | 150 | -150 | 100 | 50 | -50 |
4 | 2020 | Nigeria | Abuja | 50 | 10 | -40 | 0 | 0 | 0 |
5 | 2021 | Nigeria | Lagos | 30 | 20 | -10 | 0 | 0 | 0 |
6 | 2022 | Nigeria | Abuja | 55 | 40 | -15 | 50 | 10 | -40 |
7 | 2020 | Zimbabwe | Harare | 60 | 20 | -40 | 0 | 0 | 0 |
8 | 2021 | Zimbabwe | Harare | 30 | 25 | -5 | 60 | 20 | -40 |
9 | 2022 | Zimbabwe | Harare | 50 | 19 | -31 | 30 | 25 | -5 |
Please can anyone help me here
Thanks,
Joseph
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @AnthonyJoseph ,
These three measures are below:
Prior year sales =
VAR _prior =
TOPN (
1,
FILTER (
ALLSELECTED ( 'Table' ),
[Country] = SELECTEDVALUE ( 'Table'[Country] )
&& [City] = SELECTEDVALUE ( 'Table'[City] )
&& [Year] < SELECTEDVALUE ( 'Table'[Year] )
),
[Year], DESC
)
RETURN
SUMX ( _prior, [Sales] )
Prior year profit =
VAR _prior =
TOPN (
1,
FILTER (
ALLSELECTED ( 'Table' ),
[Country] = SELECTEDVALUE ( 'Table'[Country] )
&& [City] = SELECTEDVALUE ( 'Table'[City] )
&& [Year] < SELECTEDVALUE ( 'Table'[Year] )
),
[Year], DESC
)
RETURN
SUMX ( _prior, [profit] )
Prior year difference = [Prior year profit]-[Prior year sales]
Pbix file in the end you can refer.
Best Regards
Community Support Team _ chenwu zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks @v-chenwuz-msft I can see the data populated correctly without any slicer selection (from year) but when I choose a year from the slicer, all previous year values are turing to be 0.
Without any slicer selection I can see previous year data (example 2022 - India)
If selection is made then the preious year value is blank/zero.
Please can you help me to show previous value even when the slicer selection is made.
Thanks

Helpful resources
Subject | Author | Posted | |
---|---|---|---|
04-16-2024 07:40 PM | |||
12-01-2023 11:47 AM | |||
05-14-2024 09:50 AM | |||
06-03-2024 10:16 PM | |||
02-01-2023 11:36 AM |
User | Count |
---|---|
14 | |
14 | |
11 | |
11 | |
8 |