Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

measure

Hello, Can you help with the following please? There are three tables in model... PTable ---------- PKey PCode myDate --------- DateKey DateValue calendarDateYear CalendarQuarter StartQu...
Need Help
Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi,
The Date table has a column called StartQuarterYearMonthDayKey which has values like YYYYMMDD i.e. 20150105
or 20191005, etc.
Hope this helps

Thank you

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi Anonymous ,

Could you please provide some sample data in these three tables and the based fields if they have any relationship among them be created in order to provide you the suitable measures? Thank you.

In addition,  you can refer the methods in the following threads to write Measure_1 and Measure_2:

Previous day value

Previous Day Value comparing

Best Regards

Rena

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Portfolio table
    ---------------
    PortfolioKey i.e. 87653
    PortfolioCode i.e. XYZABC

    Date table
    -----------
    DateKey i.e. 20190907
    DateValue i.e. 2014-11-01
    CalendarQuarter i.e.
    PreviousDateKey i.e. 20190906
    NextDateKey i.e. 20190908
    PreviousMonthKey
    CalendarQuarterKey i.e. 20141
    TradingDay i.e. yes --> working days i.e no weekends
    ...

    The table below shows the start of the MonthDay which falls in the quarter
    example:
    from yyyy0105 to yyyy0404 the quarter is 1
    from yyyy0405 to yyyy0704 the quarter is 2
    from yyyy0705 to yyyy1004 the quarter is 3
    from yyyy1005 to NextYear0103 the quarter is 4

    QuarterDateStart table
    -----------------------
    QuaretrKey QuarterStartDateKey
    1 0105
    2 0405
    3 0705
    4 1005

    Fact table
    -----------
    Id --identity column
    DateKey i.e. 20190604
    PortfolioKey i.e. 865465
    Price i.e. 78.0968666888 -- decimal(38, 12)

    I would like to have two columns in the fact table:
    Measure_1 = PreviousPrice
    Measure_2 = (The Price which falls for the DateKey which matches the date in QuarterDateStart.QuarterStartDateKey)

    Please note that I can get the Quarter from the Date table for the Fact table DateKey but it is not always accurate because the QuarterDateStart table has the quarter for the monthday shown in table...

    Hope this helps
    Thank you

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Anonymous ,

      You can create the measures with below formulas:

      Measure_1 =
VAR curdate =
    MAX ( 'Fact'[DateKey] )
VAR predate =
    CALCULATE ( MAX ( 'Fact'[DateKey] ), 'Fact'[DateKey] < curdate )
RETURN
    CALCULATE ( MAX ( 'Fact'[Price] ), 'Fact'[DateKey] = predate )
      Measure_2 =
CALCULATE (
    MAX ( 'Fact'[Price] ),
    FILTER (
        ALL ( 'Fact'[DateKey] ),
        RIGHT ( 'Fact'[DateKey], LEN ( 'Fact'[DateKey] ) - 4 )
            = MAX ( 'QuarterDateStart'[QuarterStartDateKey] )
    )
)

      And I created a sample pbix file, you can get it from this link.

      Best Regards

      Rena

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        I get error in Measure_2

        The syntax for ')' is incorrect. ...