Power BI Measure to Return Last Value of Week

[boot_heel]

Hey All, 

I have successfully created a measure that calculates a backwards look at equipment inventory levels by subtracting the net of equip moved in and out of inventory from the current day's level of inventory (yes I would love to have an actual record of daily inventory level, but that's a  different organizational issue).

Here is the code I'm using to create the backwards calculated inventory:

Historical Net Inventory = 
VAR _totalInv = 

/* Net Value of items in inventory */
SUM(
    CurrentInventory[Net Value]
)

/* Inventory movements between date of movement posting and date of current inventory load */
VAR _laterMvmt = 
CALCULATE(
    SUM(
        DailyInventoryMovements[Net Mvmt Value]
    )
    ,FILTER(
        ALL(
            DailyInventoryMovements
        )
        ,DailyInventoryMovements[PostingDate]>=MIN('Date Table'[Date])
    )
)

RETURN
_totalInv - _laterMvmt

The leftmost table below shows the output rolled up by week number, while the right table shows the last two weeks expanded by date. Basically the current inventory figure is associated with today's date, and I have inventory movements from previous days that are subtracted from the current leve to find the past historical total inventory.

Difference between week and date breakdown

Everything is calculating correctly, however, when I roll up the information by week I want to show only the last available value for the week rather than the first value of the week (First weekday value highlighted in yellow, last weekday value highlighted in green).

Because it's a measure I can't seem to aggregate by the last value in the week.

Any ideas?

[v-shex-msft]

Hi @boot_heel ,
You can try to use below measure if it suitable for your expected result. Here are sample measure formula and snapshot of test result:

Last Value of Week =
IF (
    ISINSCOPE ( Inventory[Week Num] ),
    SUMX (
        FILTER ( Inventory, Inventory[Date] = MAX ( Inventory[Date] ) ),
        Inventory[Net Inventory Level]
    ),
    BLANK ()
)

Best Regards,
Jack Chen

[boot_heel]

Hi @v-shex-msft , 

Many thanks for looking into this, and I wasn't aware of the ISINSCOPE function or it's uses previously. However, I'm not getting the expected result, perhaps because I simplified the data a bit too much for the example in the original post. Currently, the value I'm returning is a measure based on 3 different tables: The Inventory Postings Table, today's Inventory Snapshot value, and a date table.

I might be able to merge the tables to achieve the correct results, but I'm hesitant to do so due to the performance issues I've experienced when doing so (I'm not sure if that's normal or not...)

[v-shex-msft]

Hi @boot_heel ,

Can you please share a pbix file with the expected result for test? It is hard to modify your formula without any detail data.

Notice: do mask on sensitive data before share.

Regards,

Xiaoxin Sheng

[boot_heel]

@v-shex-msft 

Thanks for your help, and sorry I didn't respond with the solution I found. It's not particularly elegant, but I created a last week day flag in my date table and then created a column in the date table with the following code to return one inventory value per week or today's inventory value.

Net Historical Inv. = IF ( 
      'Date Table'[Date]='Date Table'[IsLatestWeekday] || 'Date Table'[Date]=TODAY()
      , 'Date Table'[HistoricalInventory]
      , BLANK() 
)

I tried using your solution above, but I was not successfull in only showing the last weekday value.

I have also been given access to better data that will eliminate the need for this (I think), so I'm not too concerned about refining this solution, but thanks for your advice!