Measure Quote vs Date

[fjjohann]

Hello guys!

I need a measure that calculates the last quote if it does not have a record on the filtered date.

I have a related calendar table.

This measure will be used to calculate for example:
The current position of a stock, where [measure quote] * [qty hold]

Thank you

[v-yulgu-msft · Accepted solution]

Hi @fjjohann,

In my test, source table is named as 'Quote' and calendar table is named as 'date table'.

Create a new table using CROSSJOIN.

Cross table = CROSSJOIN('date table',VALUES('Quote'[STOCK]))

Based on the crossjoin table and source table, generate the table which lists those date records not existing in source table.

Extra date row =
EXCEPT (
    'Cross table',
    SELECTCOLUMNS ( 'Quote', "date", 'Quote'[DATE], "ST", 'Quote'[STOCK] )
)

Append those unlisted date records to source table via UNION.

Union table = UNION(Quote,ADDCOLUMNS('Extra date row',"QUOTE",BLANK()) )

Create measure for [QUOTE]. Drag relative columns from 'Union table' into table visual.

Measure QUOTE =
CALCULATE (
    LASTNONBLANK ( 'Union table'[QUOTE], 1 ),
    FILTER (
        ALLEXCEPT ( 'Union table', 'Union table'[STOCK] ),
        'Union table'[DATE] <= MAX ( 'Union table'[DATE] )
    )
)

I have uploaded my pbix file for your reference.

Best regards,
Yuliana Gu