Forum Discussion

fjjohann's avatar
fjjohann
Frequent Visitor
9 years ago
Solved

Measure Quote vs Date

 

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

Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    9 years ago

    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

1 Reply

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    9 years ago

    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

Related content