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

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Measure_2 seems to give the same value for all the rows which is not correct