Last 4 weeks sales from specific date

Question

Hi All,&nbsp;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.&nbsp;I used this link as reference:&nbsp;Need help displaying values before/after a specifi... - Microsoft Fabric Community&nbsp;&nbsp;&nbsp;Then I use this formula.New 4 Week Prior = CALCULATE(sum('Sales Accumulation'[Dollar Sales]),filter(all('Calendar'),'Calendar'[Week Rank]&gt;=max('Calendar'[Week Rank])-4 &amp;&amp; 'Calendar'[Week Ending]=2024-02-18)) &nbsp;But my card comes up blank. I want to get the sales totals highlighted from my week ending dates on my sales table.&nbsp;I have also tried Datesinperiod, dateadd, datesbetween and yet to be successful.Any help is appreciated. I hope it is easy for you. TIA.&nbsp;P.S. I will have to do this for prior x week periods as well as post x weeks.

ashish_mathur · Answer

Hi,
Assuming the relationships are set up properly, create a Date slicer from the Calendar Table and select 18/2/2024 there.&nbsp; Now write this measure to get the total sales for the 4 weeks period ended the selected date
Measure =&nbsp;CALCULATE(sum('Sales Accumulation'[Dollar Sales]),Datesbetween('Calendar',min(calendar[date])-27,max(calendar[date])))
Hope this helps.

lbendlin · Answer

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 · Answer

Hi&nbsp;Anonymous&nbsp;,
I see that&nbsp;lbendlin&nbsp;&nbsp; asks you for more details as well as&nbsp;Ashish_Mathur&nbsp; provides a formula.
I&nbsp; 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] &gt;= StartOfCurrentWeek
                &amp;&amp; 'Table'[date] &lt;= StartOfWeek
        )
    )
RETURN
    IF (
        MAX ( 'Table'[date] ) &gt;= StartOfCurrentWeek
            &amp;&amp; MAX ( 'Table'[date] ) &lt;= 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] &gt;= StartOfCurrentWeek
                &amp;&amp; 'Table'[date] &lt;= StartOfWeek
        )
    )
RETURN
    _re

&nbsp;
How to Get Your Question Answered Quickly - Microsoft Fabric Community
&nbsp;
If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .
&nbsp;
Best RegardsCommunity Support Team _ Rongtie
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
&nbsp;
&nbsp;
&nbsp;
&nbsp;
&nbsp;

anonymous · Answer

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 · Answer

Hi Ashish,&nbsp;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.&nbsp;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))&nbsp;Then add:Four Week Sales Prior = &nbsp; &nbsp; var eightweeksales =calculate(sum('Sales Accumulation'[Dollar Sales]),DATESINPERIOD('Sales Accumulation'[Week Ending Date],max('Sales Accumulation'[Week Ending Date]),-56,day))&nbsp; &nbsp; return&nbsp; &nbsp; (eightweeksales - [Four week sales])&nbsp;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.&nbsp;When I change the week ending date on slicer, the 4 week calculation seems numbers were slightly off &lt;2%.Here is a quick example for some data and then expect result:Week EndingSales&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;12/24/2023100&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;12/31/2023150&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1/7/2024200&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1/14/2024350&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1/21/2024100&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1/28/2024125&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2/4/2024200&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2/11/2024250&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2/18/2024205&nbsp;Key Date = 2/15/2025&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2/25/2024180&nbsp;4 Week Prior = 780&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;3/3/2024150&nbsp;4 Week Post = 770&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;3/10/2024190&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;3/17/2024250&nbsp;Then I would do a Change = divide(4 Week Post-4 Week Prior),4 Week Prior,0)3/24/2024225&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;3/31/2024280&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;4/7/2024300&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;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.&nbsp;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.&nbsp;I appreciate your help.MT

Forum Discussion

Related content

Help With Semantic Model - Different Sums & Counts for Sales in Specific Counties From Total Sales

Last week sales issue

Previous Week Sales

Sales for Specific Year (DAX)

Sales by Day, week and month