Search code examples
powerbidaxpowerquerypowerbi-desktop

DAX measure for dynamical calculation of value based on previous date

I have four tables: Daily Inventory, AFPO, RESB, and EKPO.

Daily inventory:

Material ID Date Added Total Value
1 08/19/2024 50
2 08/19/2024 100
3 08/19/2024 1000

AFPO and EKPO have almost the same headers and data, they are both incoming materials which will increase inventory value:

EKPO:

Material ID Delivery Date Value
1 08/19/2024 50
2 08/20/2024 100
3 08/21/2024 1000
1 08/20/2024 55
3 08/24/2024 500

AFPO:

Material ID Basic finish date Value
1 08/19/2024 40
2 08/20/2024 60
3 08/21/2024 300
1 08/20/2024 155
3 08/24/2024 250

RESB is the table with requirements with the same headers as AFPO but the value needs to be subtracted from the Daily Inventory total value.

RESB:

Material ID Requirements Date Value
1 08/19/2024 200
2 08/20/2024 300
3 08/21/2024 2000
1 08/20/2024 150
3 08/24/2024 700

I am trying to create a measure within "Date" table (it is connected to AFPO, EKPO, RESB, and Daily Inventory via Date column with other date type columns) which has one column "Date" starting from current day. Here is the screenshot of the relationships between the tables:enter image description here

The measure should provide how the "Total Value" from Daily Inventory will change over the next 7 days.

Date:

Date
08/19/2024
08/20/2024
08/21/2024
08/22/2024
08/23/2024
08/24/2024
08/25/2024
08/26/2024
08/27/2024

Here is the measure I have tried which almost provides the desired result:

Inventory Forecast = 
VAR _today = TODAY()
VAR _sum =
    SUM ( 'DailyInventory'[Total Value])
VAR _else =
    SUM ( 'AFPO'[Value] ) + SUM ( 'EKPO'[Value] )
        - SUM ( 'RESB'[Value] )
-- Forecast for today's date
VAR _forecast_today =
    CALCULATE (
        SUM ( 'DailyInventory'[Total Value] ),
        'Date'[Date] = _today
    )
-- Forecast for the next 7 days
VAR _forecast_7_days =
    CALCULATE (
        SUM ( 'DailyInventory'[Total Value]),
        DATESINPERIOD ( 'Date'[Date], _today, 7, DAY )
    )
RETURN
    SWITCH (
        TRUE(),
        MAX ( 'Date'[Date] ) = _today, _forecast_today + _else,
        MAX ( 'Date'[Date] ) > _today && MAX ( 'Date'[Date] ) <= _today + 7, _forecast_7_days + _else,
        _sum
    )

The issue with this measure is that for every next day, it takes current day's (08/19/2024) "Daily Inventory" total value rather than taking previous day Total value after EKPO, AFPO, and RESB summation.

While this measure would work for the first day 8/19/2024, for other dates it will not. For example: For 08/21/2024, it will use 08/19/2024 Total value and subtract the requirements from 8/21/2024 instead of taking value of 08/20/2024 after all the summation.

I would appreciate any help.

Solution

  • Try this measure:

    Inventory Forecasted (final) = 
VAR xDays = 7 //Range
VAR thisDate = MAX('Date'[Date]) 
VAR _today = TODAY()
VAR todayRange =  _today + xDays

VAR currentInv = //Gets today's inventory
    CALCULATE (
        SUMX(ALL('DailyInventory'), [Total Value]),
        DailyInventory[Date Added] = _today 
    )

VAR _AFPO = //sums Value between this date and today ignoring relationships
    CALCULATE (
        SUM(AFPO[Value]),
        AFPO[Basic finish date] <= thisDate && AFPO[Basic finish date] >= _today,
        REMOVEFILTERS()
    )
VAR _EKPO = //sums Value between this date and today ignoring relationships
    CALCULATE (
        SUM ( 'EKPO'[Value]),
        EKPO[Delivery Date] <= thisDate  && EKPO[Delivery Date] >= _today,
        REMOVEFILTERS()
    )
VAR _RESB = //sums Value between this date and today ignoring relationships
    CALCULATE (
        SUM ( 'RESB'[Value]),
        RESB[Requirements Date] <= thisDate  && RESB[Requirements Date] >= _today,
        REMOVEFILTERS()
    )
RETURN IF(thisDate >= todayRange, BLANK(), currentInv + _AFPO + _EKPO - _RESB ) //Return Blank if date outside of range

    This measure will sum the DailyInventory[Total Value] for today, then for each following day (over 7 days), the summation (AFPO + EKPO - RESB) will be added to a rolling total.

    Date Inventory for Today AFPO EKPO RESB (AFPO + EKPO - RESB) Inventory Forecasted (final)
    8/20/2024 1150 340 1050 2200 -810 340
    8/21/2024 60 100 300 -140 200
    8/22/2024 0 0 0 0 200
    8/23/2024 0 0 0 0 200
    8/24/2024 155 55 150 60 260
    8/25/2024 250 500 700 50 310
    8/26/2024 200 20 100 120 430
    8/27/2024 400 50 200 250