trying to calculate cumulative quantities over time, with no Transactions on certain dates

[EddyGD]

I am trying to calculate cumulative quantities over time, but the issue I am facing is that when there are no transactions on a particular day, the measure doesn't consider the last calculated value. How can I solve this.

The below is the measure I am using and explination:

'Main Hastings-N22 Main Transaction (3)' is my transaction table with Item#, TRANSACTION_DATE and QTY as colums. QTY has positive and negative values, If the transaction is adding to the inventory the value will be positive and vise-versa.

'Main Haistings-N22 First Available' is a table that holds my first ever inventory and has Item#, AVAILABLE_QTY as columns. Item# holds unique values.

Main Availability =

VAR SelectedDate = MAX('Main Hastings-N22 Main Transaction (3)'[TRANSACTION_DATE])

VAR Availableondate =

    CALCULATE(

        SUM('Main Haistings-N22 First Available'[AVAILABLE_QTY]) +

        SUM('Main Hastings-N22 Main Transaction (3)'[QTY]),

        'Main Hastings-N22 Main Transaction (3)'[TRANSACTION_DATE] <= SelectedDate,

        ALL('Main Hastings-N22 Main Transaction (3)'[TRANSACTION_DATE])

    )

RETURN

    Availableondate

The values circuled are the result of the measure and they are correct but the rest are the values from my 'Main Haistings-N22 First Available'[AVAILABLE_QTY]) and in this case it should stay 5000 till 12/6/2023, but in this period I have no transactions.

[EddyGD]

Thank you @Daniel29195 the solution worked perfectly!

[Daniel29195 · Accepted solution]

@EddyGD 

i would suggest to create w valid dimdate table, --> https://radacad.com/create-a-date-dimension-in-power-bi-in-4-steps-step-1-calendar-columns

STEP 2 : link this table to the transaction date column  to both tables fact tables you have in your model

STEP 3 : drag and drop date column from dimdate table to the visual instead of the one from the existing table

step3 : 

Main Availability =

VAR SelectedDate = MAX(dimdate[date])

VAR Availableondate =

    CALCULATE(

        SUM('Main Haistings-N22 First Available'[AVAILABLE_QTY]) +

        SUM('Main Hastings-N22 Main Transaction (3)'[QTY]),

        dimdate[date]  <= SelectedDate,

        ALL(dimdate)

    )

RETURN

    Availableondate

tell me if it works out for you . 

If my response has successfully addressed your issue kindly consider marking it as the accepted solution! This will help others find it quickly. I would appreciate hitting that kudos button 👍🫡