measure

[Anonymous]

Hello,

Can you help with the following please?
There are three tables in model...

PTable
----------
PKey
PCode

myDate
---------
DateKey
DateValue
calendarDateYear
CalendarQuarter
StartQuarterYearMonthDay --> example of the existing data - following logic. no need to do anything here
i.e. if [Calendar Quarter] = 1 then CalendarDateYear & 0105
i.e. if [Calendar Quarter] = 2 then CalendarDateYear & 0405
i.e. if [Calendar Quarter] = 3 then CalendarDateYear & 0705
i.e. if [Calendar Quarter] = 4 then CalendarDateYear & 1005

Fact
--------
DateKey
PKey
UnitPrice
...

Question:
-------------
Using DAX, how is it possible to work out a measure for each row as follows:

In Fact Table, I would like to create two measures
Measure_1 = Previous day UnitPrice
Measure_2 = UnitPrice in Fact Table which matches the DateKey of StartQuarterYearMonthDay in myDate table

[amitchandak]

@Anonymous , are you have a calendar that starts from 5th of every year and each qtr from 5th ?

[Anonymous]

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]

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]

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]

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]

I get error in Measure_2

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

[Anonymous]

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

[Anonymous]

Hi @Anonymous ,

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

Best Regards

Rena