Previous week per category

[sabd80]

Hi,
I would like to get last week volume per category.

Below is the total volume for 2 dates per category.

the measure is: 
Volume = sum(table[volume])+0

I want to get the volume of the previous week, the volume for category 1 is 0. because current week does not have category 1

Volume LW =
VAR CurWK = SELECTEDVALUE('table'[Date])
VAR PreWeek = CurWK-7
RETURN
CALCULATE([Volume],table[Date] = PreWeek)

what I want is to have 57 under category 1 in above visual.

Can you please help?

[sabd80 · Accepted solution]

I found the solution from this site:
https://forum.enterprisedna.co/t/how-can-i-get-the-53-week-last-year-to-appear/18111/14

[Anonymous]

Hi @sabd80 ,

Thanks to Jpss  and Ashish_Mathur  for their interest in this issue. I have some other thoughts to add:

(1) This is my test data. 

(2) We can create a measure. 

Measure = CALCULATE(SUM('Table'[volume]),FILTER(ALLSELECTED('Table'),[category]=MAX('Table'[category]) && [Date]=MAX('Table'[Date])-7))

(3) Then the result is as follows.

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

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

[Ashish_Mathur]

Hi,

Share the download link of the PBI file with a Calendar table and week number column already built there.  Ensure that the visual is built as well.

[Jpss]

hey @sabd80 
here is the solution i created for you. 

I created it with following measure. 

lastWeekVolume = CALCULATE(SUM('Table'[Value]), DATEADD('Table'[Date],-7,DAY))

I transformed the dataset in following format for this measure 

I hope this helps. 

[sabd80]

Hi @Jpss ,

It did not work, because I don't have the records for the zero values in the data.

Yours work because you have the records for zero values.

Also the pbix file you have sent me is different from the screenshot you have provided

[Jpss]

Hey, 
I created the solution based on the screenshot you shared.  It will be helpful if you can share a working PBIX file  that demonstrates your use case scenario and issue you have described above

[sabd80]

Please remove the zero values in your data, that should help in creating the new measure.