How to show past four years sales by qtr using DAX

Hello All,

I am working on a project where I want to show something similar but years as horizontal and qtrs as vertical for sales. So want to show 5 years past sales data by qtr depending on which year I select in the slicer.

E.g.:
Qtr 2019 2020 2021 2022 2023 2024
Q1
Q2
Q3
Q4

Total

I read a thread below which is very close to what I am thinking but need little bit different (as mentioned above in example)

Thanks in advance

This doesn’t sound too difficult to me if you have a date table.

Check out what Data Mentor for this unit it says the same thing.

You just need a date table and connect it to your sales table.

You don’t even need to create the date table as per the thread here.

All you need to do is have a detailed date table and connect it to the appropriate dates column and your sales table

Thanks @SamMcKay

I will try that but as far as data goes, will I be able to attach my current PBIX file here so you can see (sorry I am new here so not sure how to share that)?

@SamMcKay

I did created a new date table but still not getting what I am expecting. I created separate measure for each and as long as I am selecting year 2024 from the slicer it’s working fine but as soon as I select any other year, data does flow but headers doesn’t match to data. I attached dummy PBIX file for you to check.

Thanks
Sales Data from 2019 to 2023-Test.pbix (131.3 KB)

Hi @SamMcKay

Any update/help?

Thanks

See if it helps
Sales Data from 2019 to 2023-Test.pbix (131.7 KB)
Captura de tela 2024-03-30 151020

Sorry @VilmarSchi on delay in response.

Your solution is much cleaner and easy to follow than what I came up with. Appreciate! I will take this a solution/Answer.

One quick question. How can I make first FIVE columns locked regardless which year is selected from the slicer. E.g.: If someone select 2022, I want to show LRF, CRF, WP (along with differences for the current year).

Thanks Again!

@AlokBhardwaj, glad it went right. But I suggest you close this topic and open another one please.

(post deleted by author)