Previous Week Sum Measure

[clarkey1988]

Hi, I'm a fairly new Power BI user and am having a problem calculating a measure for a Matrix visual.

My aim is to calculate the previous weeks miles for each business division in a Matrix. My current function calculates the total previous week miles. However, I need it to calculate the previous weeks miles for each business division.

Previous Week Miles = CALCULATE(SUM('Trip Detail'[Miles]),(FILTER(ALLSELECTED('Trip Detail'),'Trip Detail'[Week Number]=MAX('Trip Detail'[Week Number])-1)))

My overall goal is to calculate weekly % change by business division. Please could someone push me in the right direction?

[clarkey1988]

Thank you all for your responses!

[amitchandak]

@clarkey1988 , Make sure week is in a separate table. In date table or week Table.

Refer my blog

https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...

[PaulDBrown]

@clarkey1988 

You don't need ALLSELECTED. What is your current week measure? 

You probably only need something along the lines of:

Previous week = CALCULATE(SUM('Trip Detail'[Miles]),
FILTER(ALL('Trip Detail'),
'Trip Detail'[Week Number]=MAX('Trip Detail'[Week Number])-1))

If there are other row contexts (nested for example) you might need ALLEXCEPT

Something along the lines of:

Previous week = CALCULATE(SUM('Trip Detail'[Miles]),
(FILTER(ALLEXCEPT('Table, Table[Business Division]'),'Trip Detail'[Week Number]=MAX('Trip Detail'[Week Number])-1)))

[clarkey1988]

Hi @PaulDBrown 

Thanks for responding. My current measure is simply the sum of the current weeks miles/sum of week 10 miles (pre COVID)

Δ Miles = (SUM('Trip Detail'[Miles]))/(CALCULATE(SUM('Trip Detail'[Miles]),'Trip Detail'[Week Number]=10))-1

However, I need it to be week over week and not specific to week 10. I have two filters within the Matrix, but it seems the measure you listed without ALLSELECTED still sums the TOTAL miles, and does not break it out by business division. The business division is a column I added and made into an if statement.

[PaulDBrown]

@clarkey1988 
Hi again,

You probably need to include the fweek number in the ALL clause:

Previous week = CALCULATE(SUM('Trip Detail'[Miles]),
FILTER(ALL('Trip Detail'[Week Number]),
'Trip Detail'[Week Number]=MAX('Trip Detail'[Week Number])-1))

Otherwise try with the second option I posted using ALLEXCEPT, see if that works. 

[clarkey1988]

@PaulDBrown  so I think I'm close. The first optuion did not work

The ALLEXCEPT version below calculatues the previous weeks miles by business division accurately. However, it does not take into considertation the two filters I have in the matrix.

Previous Week = CALCULATE(SUM('Trip Detail'[Miles]),

(FILTER(ALLEXCEPT('Trip Detail', 'Trip Detail'[Business Division]),'Trip Detail'[Week Number]=MAX('Trip Detail'[Week Number])-1)))

[v-shex-msft · Accepted solution]

HI @clarkey1988,

I think you also need to consider about the year conversion issue, so I added if statement your formula to dispatch to two conditions based on current week number that extract from your table date fields. (I add one additional condition to your formula to filter records based on the year and week number, it will be filtered more accurately if your table contains many year records)
You can try it if below formula meets to your requirement:

Previous Week =
VAR currDate =
    MAX ( 'Trip Detail'[date] )
RETURN
    CALCULATE (
        SUM ( 'Trip Detail'[Miles] ),
        FILTER (
            ALLSELECTED ( 'Trip Detail' ),
            IF (
                weekNumber > 1,
                YEAR ( 'Trip Detail'[Date] ) = YEAR ( currDate )
                    && WEEKNUM ( 'Trip Detail'[Date] )
                        = WEEKNUM ( currDate ) - 1,
                YEAR ( 'Trip Detail'[Date] )
                    = YEAR ( currDate ) - 1
                    && WEEKNUM ( 'Trip Detail'[Date] )
                        = WEEKNUM ( DATE ( YEAR ( currDate ) - 1, 12, 31 ) )
            )
        ),
        VALUES ( 'Trip Detail'[Business Division] )
    )

Regards,

Xiaoxin Sheng

[Jayasreeb]

Hi @v-shex-msft  , your answer helped with my Last Calendar week Orders calculation.I am struggling to calculate orders for Last 4 Calendar weeks. Any help with that will be greatly appreciated.

[PaulDBrown]

@clarkey1988 

Ok, so we are getting close...

Something which I din't ask, and is actually very important (for many reasons, including these kind of calculations): do you have a calendar or Date table?

If not, please create one and link it to your tables in one-to-many relationship.

(As regards the measure not taking into account the filters, it's hard to see what's going on without knowing what/if tables & relationships you are enacting in the filters...).

Ps, it would be very helpful if you could provide a sample PBIX file/sample data (with fake data of need be) to work on!