Need help in Dax calculation to show sales value of Current Week of Current year with last two years

[Anonymous]

Hi Community,

I need help in creating DAX Measure that calculates sales of latest week of current year i.e. 2021 then it should show the sales value of same week number of previous year i.e. 2020 and same week number for 2019 as well, let whatever the dates are for same week number doesn't matter. Refer Screenshot below:

Example: if Week - 50 is the current week for 2021 then when we hit Weekly button then the matrix should show the value for Week - 50 for 2020 & 2019.

Looking forward to your kind response

Thanks in advance!

Regards

Manoj

[v-robertq-msft · Accepted solution]

Hi, 

According to your description, I can roughly understand your requirement, I think you can achieve this using some calculated columns and a measure, you can try this step:

This is the test data I created based on your description:

Create two calculated columns:

Week = "Week - "&WEEKNUM('Table'[Date])

Year = YEAR('Table'[Date])

Create a measure:

Measure = SUM('Table'[Amount])

And you can create a matrix and a slicer to place them liks this to get what you want:

You can download my test pbix file below

If this result is not what you want, you can post some sample data(without sensitive data) and your expected result.

How to Get Your Question Answered Quickly 

Thank you very much!

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[VahidDM]

Hi @Anonymous 

Do you have a Calendar [Date] table in your model? If not, sue this link to add it to your model:

https://www.vahiddm.com/post/creating-calendar-table-with-3-steps

then you can use the Week No. column in the Rows and Year in the columns and add value to the Matrix.

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[bcdobbs]

I don't think you need any fancy DAX for this although may be missing a requirement.

What you need is a date table which has the following columns: Date, Week Number, Year.

A relationship from date to date on your sales table will be one to many.

Then on a matrix you can drop Week Number on the rows and year on the columns.

Lots of ways of creating a date table but you could start here: Creating a simple date table in DAX - SQLBI