sum of latest 8 weeks

[Anonymous]

I have 2 columns data and weeknum.

I have 2 things to ask

1.I want to get sum of 8 weeks 

2. Use “Relative filtering” to keep current minus 7 weeks

Let me know how can it be done

for 8 weeks im using

Sum of 8 weeks =

CALCULATE (

SUM ( 'Open Req'[Committed to Start - Candidate Did Not Start] ),

FILTER ('Open Req',

WEEKNUM ( 'Open Req'[Creation date] )

>= WEEKNUM ( TODAY () ) - 7

&& WEEKNUM ( 'Open Req'[Creation date])

<= WEEKNUM ( TODAY () ) + 1))

It is giving a large amount which is wrong.

Edit: Weeknum of 1 to 8 belongs to january month , so i think im also using wrong weeknum

[v-easonf-msft · Accepted solution]

Hi, @Anonymous 

As mentioned by @edhans ,you need to create a calendar table.

Then create measure as below:

latest_weeknum = WEEKNUM (CALCULATE( MAX ( 'Open Req'[Creation date] ),ALL('Open Req' )))

Sum of 8 weeks = 
VAR latest =
    MAX ( 'CTS-Starts base'[Creation date] )
RETURN
    CALCULATE (
        SUM ( 'Open Req'[Committed to Start - Candidate Did Not Start] ),
        FILTER (
            'Calendar',
            WEEKNUM ( 'Calendar'[Date] )
                > WEEKNUM ( latest ) - 7
                && WEEKNUM ( 'Calendar'[Date] )
                    < WEEKNUM ( latest ) + 1
        )        
    )

We also need to create a measure,then apply it to visual filter pane.

visual control = 
VAR weeknum =
    SELECTEDVALUE ( 'Calendar'[WeekNum] )
RETURN
 IF ( weeknum >= 'latest date of creation date'[latest_weeknum]- 7 && weeknum <= 'latest date of creation date'[latest_weeknum] , 1, 0 )

The result will show as below:

Best Regards,
Community Support Team _ Eason

[mahoneypat]

Please use this measure expression patter to get your result.

Last 8 Weeks =
VAR todaysdate =
    TODAY ()
VAR enddate =
    todaysdate - 49
        - WEEKDAY ( todaysdate ) + 1
RETURN
    CALCULATE (
        [YourMeasure], //or expression
        FILTER (
            ALL ( 'Date'[Date] ),
            'Date'[Date] <= todaysdate
                && 'Date'[Date] >= enddate
        )
    )

Regards,

Pat

[Anonymous]

@mahoneypat,

Could you explain  why you added current week day : Weekday(todaysdate)+1

Your method is giving me result 0 which is wrong but it should be 9.

Power bi file -Link 

[mahoneypat]

That filter goes back to Oct 3rd.  When I filter your date to >= that date, all the rows are 0 in that column, so the result may be correct.  If you change 49 to 56, the result is 3.

Regards,

Pat

[edhans]

You need a data table. the filter the week number in your date table in the filter pane. I have a full date table here that the weeks start on Sunday - the 2nd or 3rd line of code - Day.Sunday. Change that to be whatever you want. Then filter on the [Week of Year] column in the filter pane telling it week number 8 or less. (or 7, or whatever you need.)

Beyond that, I'd need some data to work with and a sample of expected results.

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum

[Anonymous]

@edhans,Thanks for your reply , But i'm looking a way to do it via DAX.

[edhans]

Understood @Anonymous. Note that my solution doesn't solve it in Power Query. It just provides the date table that way. You still solve it in DAX. You just need a good date table, and the best methods for creating a date table are either in the source system (many ERP systems have great date tables) or in Power Query which to Power BI looks like a source table. 

You'd still need DAX to finalize it as DAX is great for analysis.