Missing dates in fact table

[Johan]

Hi,

I've got these 2 tables:

calendar

datekey

01-01-2019

02-01-2019

03-01-2019

fact

datekey        stock

01-01-2019  50

03-01-2019   70

Report needs to be:

Stock levels:

datekey        stock

01-01-2019  50

02-01-2019   50

03-01-2019   70

So, if a date is missing, the latest value must be taken.

I prefer to use dax for this.

Anyone has a suggestion?

Thanks,

Johan

[Zubair_Muhammad · Accepted solution]

@Johan 

Hi,

Try this MEASURE

Measure =
CALCULATE (
    SUM ( 'fact'[stock] ),
    LASTNONBLANK (
        FILTER (
            ALLSELECTED ( 'Calendar'[Datekey] ),
            'Calendar'[datekey] <= SELECTEDVALUE ( 'Calendar'[datekey] )
        ),
        CALCULATE ( SUM ( 'fact'[stock] ) )
    )
)