How to duplicate values from grouped measures into additional virtual groups

Question

I have a table with places and corresponding population per year. Currently I only have data up to year 2018. Somethin like this:

+-------+------+------------+
| Place | Year | Population |
+-------+------+------------+
| a     | 2017 |         12 |
| a     | 2018 |         11 |
| b     | 2017 |         43 |
| b     | 2018 |         21 |
+-------+------+------------+

I've created a measure that sums the "Population" column values and used it in a columns chart with the "Year" column as the axis to see the total population per year. The data in the chart would be:

+------+-------+
| Year | Total |
+------+-------+
| 2017 |    55 |
| 2018 |    33 |
+------+-------+

I need the chart to also show years 2019 and 2020 with the same value from 2020, like:

+------+-------+
| Year | Total |
+------+-------+
| 2017 |    55 |
| 2018 |    33 |
| 2019 |    33 |
| 2020 |    33 |
+------+-------+

How can I achieve the above via DAX. I basically need to take the "Total" from the MAX "Year" and dynamically create two additional entries for 2019 and 2020 with the same total from that MAX "Year".

My use case is more involved as then I will have to add another measure to the same columns chart to show how many of that total population attended a particular class. My table with the "class attendance" data does have values up to 2020 which is why I need the total population to go all the way to 2020 and assume that the population for 2019 and 2020 is the same than in 2018 so then I can compare that with class attendance numbers.

Any help or guidance is welcomed.

Thanks.

Answer 1

This can be achieved very easily by power query: (may be there will be a way to do it in measures as well)

Assuming you data would be something like this:

You can go to Transform tab and perform group by like below

Now Select the Fill(Down) in the Transform tab like below

Finally you can in the visualization tab you can create below: