When data for a calendar day is missing ... Power Query or DAX solution?

Question

I have a table that should provide a row per calendar day and is refreshed several times a day. The data is the current stock level of a warehouse. Based on the data, two measures make a simple 7-day simulation of expected warehouse space. The calculation is cumulative and quite straightforward and the main measure is below.

Occasionally there isn't a row for "NetOfDeliveryCollection" on a calendar day so the simulation goes awry. I think I just need to add a value of zero for that missing day but my quesiton is how? Should I create a fake row in the dataload script or just add an IF condition in the DAX.

Either method, I don't know how to do this!

Measure = SUM(DailyBalances[CurrBalance])
+ CALCULATE(
SUM(Delivery[NetOfDeliveryCollection])
, FILTER(
ALLSELECTED(Delivery)
,Delivery[DelDate] <= MAX(Delivery[DelDate])
)
)

v-zhangti · Accepted Answer

Hi,&nbsp;Anonymous&nbsp;
&nbsp;
You can add IF conditions to the DAX.&nbsp;This is shown below.
Measure =
VAR N1 =
    SUM ( DailyBalances[CurrBalance] )
        + CALCULATE (
            SUM ( Delivery[NetOfDeliveryCollection] ),
            FILTER (
                ALLSELECTED ( Delivery ),
                Delivery[DelDate] &lt;= MAX ( Delivery[DelDate] )
            )
        )
VAR N2 =
    SUM ( DailyBalances[CurrBalance] ) + 0
RETURN
    IF ( SELECTEDVALUE ( Delivery[NetOfDeliveryCollection] ) = BLANK (), N2, N1 )

&nbsp;Please try this formula, does this match your desired result?
&nbsp;
Best Regards,
Community Support Team _Charlotte
If this post&nbsp;helps, then please consider&nbsp;Accept it as the solution&nbsp;to help the other members find it more quickly.

Forum Discussion

When data for a calendar day is missing ... Power Query or DAX solution?

2 Replies

Related content

missing data

DAX SOLUTION NEEDED

Generating Missing Data

missing data

Inferring Missing Columns and Imputing Missing Data