Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
misiek5510
Helper III
Helper III

Previous Week Figure & Average of Last 4 Weeks

I'm looking to add to somehow add a measure or edit the current one to show me last full week (previous week), and an average of last 4 weeks. I want to put data in to a table filter it to show previous week, and then use to average of 4 weeks to put a conditional formating with arrows up or down depending on the value. Hope this makes sense 🙂 

 

 

 

 

View By Last X Days = 
VAR    
    _7days = ADDCOLUMNS(
         CALCULATETABLE(
            'Shared Dates'
            ,DATESBETWEEN('Shared Dates'[Date],today()-7,today() )
        )
        ,"In the last", "7 Days"
    )

        
    )
    VAR
    _mtd = ADDCOLUMNS(
         CALCULATETABLE(
            'Shared Dates'
            ,DATESBETWEEN('Shared Dates'[Date], DATE( YEAR(TODAY()), MONTH(today()), 1),today() )
        )
        ,"In the last", "Current Month"
        
    )
return 
UNION(_7days,_MTD)

 

 

1 ACCEPTED SOLUTION

I found a solution that ticks 90% of the boxes, however it takes into account the last full week too, and I wanted to look 4 weeks prior the last full week. 

 

For example we are in week 6 now (not full week), so last full week will be week 5, so the below formula should look at average of week 1,2,3 and 4, but instead its looking at 2,3,4 and 5. 

 

 

 

!INC RAG2 = 
VAR previousWeekDate =
    TODAY () - WEEKDAY ( TODAY (), 3 )
RETURN
    CALCULATE( 
        DISTINCTCOUNT( '1-182'[Inc No.] ),
        FILTER (
            'Shared Dates',
            'Shared Dates'[Date] <= previousWeekDate
                && 'Shared Dates'[Date] >= ( previousWeekDate - 28 )
        )
    ) /4

 

 

 

 

Also, would there be an option to ignore Report Slicer? As when I change the Date Slicer for the tab it messes up the above measure? 

Edit: for future generations seeking anwer to this. 
just need to put all( 'Shared Dates'), after using filter function to ignore page / visual filter. 

View solution in original post

5 REPLIES 5
misiek5510
Helper III
Helper III

My definition of week is Monday to Sunday. 

I have a table displaying last full week, so if we are in week number 6 this year it will display results for week 5. 
All the values in that table come from measures. 

What my goal is to have RAG status next to each value, which would indicate if the value is higher or lower than average of last 4 weeks. 

For example: if the table is displaying data for week 5, the RAG status will be based on the average of weeks 1,2,3 and 4. 

lbendlin
Super User
Super User

what is your definition of "week" ?  

 

Please provide sample data that covers your issue or question completely.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

I found a solution that ticks 90% of the boxes, however it takes into account the last full week too, and I wanted to look 4 weeks prior the last full week. 

 

For example we are in week 6 now (not full week), so last full week will be week 5, so the below formula should look at average of week 1,2,3 and 4, but instead its looking at 2,3,4 and 5. 

 

 

 

!INC RAG2 = 
VAR previousWeekDate =
    TODAY () - WEEKDAY ( TODAY (), 3 )
RETURN
    CALCULATE( 
        DISTINCTCOUNT( '1-182'[Inc No.] ),
        FILTER (
            'Shared Dates',
            'Shared Dates'[Date] <= previousWeekDate
                && 'Shared Dates'[Date] >= ( previousWeekDate - 28 )
        )
    ) /4

 

 

 

 

Also, would there be an option to ignore Report Slicer? As when I change the Date Slicer for the tab it messes up the above measure? 

Edit: for future generations seeking anwer to this. 
just need to put all( 'Shared Dates'), after using filter function to ignore page / visual filter. 

Please provide sanitized sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

If you read the thread properly you will see it was solved. 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.