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 calcu...
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

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