Forum Discussion

sabd80's avatar
sabd80
Helper IV
1 year ago
Solved

Previous week per category

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?

Need Help

7 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    1 year ago

    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's avatar
    Jpss
    Resolver II
    1 year ago

    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's avatar
      sabd80
      Helper IV
      1 year ago

      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's avatar
        Jpss
        Resolver II
        1 year ago

        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

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.