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

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi Anonymous ,

        Could you please provide some screen shots about your visuals and applied measures in report? Thank you.

        Best Regards

        Rena