Last Year Sales using index week column and year column

[bellspartan23]

Hi All,

I feel that I am getting close to solving this but might just be missing a few steps. I created the below Calculated Measure. The goal of this was to calculate last year sales based on an index (which is the week #; 1 - 52) and year column. The purpose of the index column was to override the week # so that it would restart after it reaches 52 v. having a 53rd week. I'm assuming it's possible to calculate this instead of using time intelligence functions. 

The output is correct on a week-to-week basis but it's not aggregating correctly. The total returns the latest week sales, which I understand why. Just not sure how to write the logic to have it aggregate as well.

LY $ =

var yr = MAX('Sales'[Year])-1

var wk = MAX('Sales'[Week #])

return

CALCULATE([Sales $],

ALL('Sales'),

'Sales'[Year] = yr,

'Sales'[Week #] = wk)

[Anonymous · Accepted solution]

Hi @bellspartan23 ,

You can create another new measure as below and put it on the visual to replace the original measure [LY $]:

Measure = SUMX ( GROUPBY ( 'Sales', 'Sales'[Year], 'Sales'[Week #] ), [LY $] )

In addition, you can refer the following links to try to solve your problem...

Why Your Total Is Incorrect In Power BI - The Key DAX Concept To Understand

Dax for Power BI: Fixing Incorrect Measure Totals

If the above one can't help you, could you please provide some raw data in your table 'Sales' (exclude sensitive data) with Text format and your expected result with backend logic and special examples? It would be helpful to find out the solution. You can refer the following link to share the required info:

How to provide sample data in the Power BI Forum

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[bellspartan23]

Hi Rena,

This is perfect! Works exactly how I needed it to. Thank you! The first column is TY sales and last column was my first pass at the LY sales calc. The middle column is using your solution. Really appreciate it!