Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Last 4 weeks sales from specific date

Hi All,

 

I have been searching for a while and while things are close and I have tried to manipulate the dax, it's not coming out. Creating this column.

 

I used this link as reference: Need help displaying values before/after a specifi... - Microsoft Fabric Community

 

 

 

Then I use this formula.

New 4 Week Prior = CALCULATE(sum('Sales Accumulation'[Dollar Sales]),filter(all('Calendar'),'Calendar'[Week Rank]>=max('Calendar'[Week Rank])-4 && 'Calendar'[Week Ending]=2024-02-18))
 
But my card comes up blank. I want to get the sales totals highlighted from my week ending dates on my sales table.

 

I have also tried Datesinperiod, dateadd, datesbetween and yet to be successful.

Any help is appreciated. I hope it is easy for you. TIA.

 

P.S. I will have to do this for prior x week periods as well as post x weeks.

Need Help

5 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    2 years ago

    Hi,

    Assuming the relationships are set up properly, create a Date slicer from the Calendar Table and select 18/2/2024 there.  Now write this measure to get the total sales for the 4 weeks period ended the selected date

    Measure = CALCULATE(sum('Sales Accumulation'[Dollar Sales]),Datesbetween('Calendar',min(calendar[date])-27,max(calendar[date])))

    Hope this helps.

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi Ashish,

       

      Sorry - I thought I had given a reply and when looking, I did not see it in the post.

      I did try it and it came up with a dax error based.

       

      I was able to use:

      Four week sales = calculate(sum('Sales Accumulation'[Dollar Sales]),DATESINPERIOD('Sales Accumulation'[Week Ending Date],max('Sales Accumulation'[Week Ending Date]),-28,DAY))
       
      Then add:
      Four Week Sales Prior =
          var eightweeksales =calculate(sum('Sales Accumulation'[Dollar Sales]),DATESINPERIOD('Sales Accumulation'[Week Ending Date],max('Sales Accumulation'[Week Ending Date]),-56,day))
          return
          (eightweeksales - [Four week sales])
       
      The numbers are accurate. The issue comes that I have to change the slicer to the corresponding week ending date to make it go back the proper amount of weeks to get to the 2-15-2024 date. In this case with latest data of 3-17-24, I use that slicer. When 9 weeks elapse, I will do the same thing, but I will have to change the date slicer.
       
      When I change the week ending date on slicer, the 4 week calculation seems numbers were slightly off <2%.
      Here is a quick example for some data and then expect result:
      Week EndingSales        
      12/24/2023100        
      12/31/2023150        
      1/7/2024200        
      1/14/2024350        
      1/21/2024100        
      1/28/2024125        
      2/4/2024200        
      2/11/2024250        
      2/18/2024205 Key Date = 2/15/2025     
      2/25/2024180 4 Week Prior = 780      
      3/3/2024150 4 Week Post = 770      
      3/10/2024190        
      3/17/2024250 Then I would do a Change = divide(4 Week Post-4 Week Prior),4 Week Prior,0)
      3/24/2024225        
      3/31/2024280        
      4/7/2024300        

       

      I was looking to make it where the date on comparison would be Week Ending 2-18-2024 as the actual price change date happened on 2-15-2024.

       

      So even though we have data in this set into April, it would still calculate the expected results without having to change the date slicer to 3-17-24.

       

      I appreciate your help.
      MT

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    2 years ago

    Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

    Do not include sensitive information or anything not related to the issue or question.

    If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

    Please show the expected outcome based on the sample data you provided.

    Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Sorry IBendlin,


      I cannot share information outside my organization. They have VERY tight security. Anything posted to SharePoint, DropBox, etc. has to be approved by IT and probably just to share would even be more complicated. I did post a response to Ashish hoping that gives him clarity. Thanks for your help.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous ,

    I see that lbendlin   asks you for more details as well as Ashish_Mathur  provides a formula.

    I  have create a simple sample, you can also refer to .

    Create 2 measures.

    RelWeekIndexmeasure =
VAR _sel =
    SELECTEDVALUE ( 'Table 2'[Date] )
VAR StartOfWeek =
    MAX ( 'Table 2'[Date] ) - WEEKDAY ( MAX ( 'Table 2'[Date] ), 2 ) + 1
VAR StartOfCurrentWeek = StartOfWeek - 28
VAR _re =
    CALCULATE (
        SUM ( 'Table'[value] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[date] >= StartOfCurrentWeek
                && 'Table'[date] <= StartOfWeek
        )
    )
RETURN
    IF (
        MAX ( 'Table'[date] ) >= StartOfCurrentWeek
            && MAX ( 'Table'[date] ) <= StartOfWeek,
        _re,
        BLANK ()
    )

    Measure = MAXX('Table',[RelWeekIndexmeasure])

    Or you can put [measure2] into the card directly.

    Measure2 =
VAR _sel =
    SELECTEDVALUE ( 'Table 2'[Date] )
VAR StartOfWeek =
    MAX ( 'Table 2'[Date] ) - WEEKDAY ( MAX ( 'Table 2'[Date] ), 2 ) + 1
VAR StartOfCurrentWeek = StartOfWeek - 28
VAR _re =
    CALCULATE (
        SUM ( 'Table'[value] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[date] >= StartOfCurrentWeek
                && 'Table'[date] <= StartOfWeek
        )
    )
RETURN
    _re

     

    How to Get Your Question Answered Quickly - Microsoft Fabric Community

     

    If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

     

    Best Regards
    Community Support Team _ Rongtie

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