measure

Question

Hello,Can you help with the following please?There are three tables in model...PTable----------PKeyPCodemyDate---------DateKeyDateValuecalendarDateYearCalendarQuarterStartQuarterYearMonthDay --&gt; example of the existing data - following logic. no need to do anything herei.e. if [Calendar Quarter] = 1 then CalendarDateYear &amp; 0105i.e. if [Calendar Quarter] = 2 then CalendarDateYear &amp; 0405i.e. if [Calendar Quarter] = 3 then CalendarDateYear &amp; 0705i.e. if [Calendar Quarter] = 4 then CalendarDateYear &amp; 1005Fact--------DateKeyPKeyUnitPrice...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 measuresMeasure_1 = Previous day UnitPriceMeasure_2 = UnitPrice in Fact Table which matches the DateKey of StartQuarterYearMonthDay in myDate table

amitchandak · Answer

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

anonymous · Answer

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

anonymous · Answer

Hi&nbsp;Anonymous&nbsp;,Could you please provide some sample data in these three tables and the based fields if they have any relationship among them be&nbsp;created in order to provide you the suitable measures? Thank you.In addition,&nbsp; you can refer the methods in the following threads to write Measure_1&nbsp;and Measure_2:Previous day valuePrevious Day Value comparingBest RegardsRena

anonymous · Answer

Portfolio table---------------PortfolioKey i.e. 87653PortfolioCode i.e. XYZABCDate table-----------DateKey i.e. 20190907DateValue i.e. 2014-11-01CalendarQuarter i.e.PreviousDateKey i.e. 20190906NextDateKey i.e. 20190908PreviousMonthKeyCalendarQuarterKey i.e. 20141TradingDay i.e. yes --&gt; working days i.e no weekends...The table below shows the start of the MonthDay which falls in the quarterexample:from yyyy0105 to yyyy0404 the quarter is 1from yyyy0405 to yyyy0704 the quarter is 2from yyyy0705 to yyyy1004 the quarter is 3from yyyy1005 to NextYear0103 the quarter is 4QuarterDateStart table-----------------------QuaretrKey QuarterStartDateKey1 01052 04053 07054 1005Fact table-----------Id --identity columnDateKey i.e. 20190604PortfolioKey i.e. 865465Price i.e. 78.0968666888 -- decimal(38, 12)I would like to have two columns in the fact table:Measure_1 = PreviousPriceMeasure_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 helpsThank you

anonymous · Answer

Hi&nbsp;Anonymous&nbsp;,You can create the measures with below formulas:Measure_1 =
VAR curdate =
    MAX ( 'Fact'[DateKey] )
VAR predate =
    CALCULATE ( MAX ( 'Fact'[DateKey] ), 'Fact'[DateKey] &lt; 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 RegardsRena

Forum Discussion

measure

8 Replies

Related content

Measure

DAX Measure

Path Measure

% of total measure

Count Measure