Forum Discussion

Johan's avatar
Johan
Advocate II
7 years ago
Solved

Missing dates in fact table

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

Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    7 years ago

    Johan 

     

    Hi,

     

    Try this MEASURE

     

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

1 Reply

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    7 years ago

    Johan 

     

    Hi,

     

    Try this MEASURE

     

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