Forum Discussion
Solved
Differences in using dates from FACT TABLE and DIMENSION TABLE
hello I'm posting because I'm having a lot of trouble creating a measure. My model has four tables. 1. DATA : Quantity, Symbol, Date 2. PRICE : Price, Symbol, Date 3. DATE : date 4. SYMBOL : SY...
Hi,
Please find attached the PBI file.
Hope this helps.
inmingoon I believe this is what you are looking for, basically, you need to work with the date table in each expression:
Max PRICE = VAR SLT_DAT = MAX ( 'Date'[DATE] ) VAR SUM_TBL = SUMMARIZE ( 'DATA', 'SYMBOL'[NAME] ) VAR PRC_DAT = CALCULATE ( MAX ( 'PRICE'[DATE] ), FILTER ( ALLSELECTED ( 'DATE'[Date] ), 'DATE'[DATE] <= SLT_DAT ), SUM_TBL ) VAR result = CALCULATE ( MAX ('PRICE'[PRICE] ), SUM_TBL, 'Date'[DATE] = PRC_DAT ) return resultinmingoon you can also take advantage of the LASTNONBLANKVALUE function, Check the video here LASTNONBLANKVALUE and Missing Data - Power BI - YouTube
Max PRICE 2 = VAR SUM_TBL = SUMMARIZE ( 'DATA', 'SYMBOL'[NAME] ) VAR result = CALCULATE ( LASTNONBLANKVALUE ( 'Date'[DATE], SUM ( 'PRICE'[PRICE] ) ), SUM_TBL, FILTER ( ALLSELECTED ( 'DATE'[Date] ), 'DATE'[DATE] <= MAX ( 'DATE'[DATE] ) ) ) return result
Greg_Deckler
Community Champion
Community Championinmingoon I would suggest this perhaps? Changed the first variable from using DATA to DATE.
PRICE =
VAR SLT_DAT = max('DATE'[DATE])
VAR SUM_TBL = summarize('DATA','SYMBOL'[NAME])
VAR PRC_DAT = calculate(max('PRICE'[DATE]),SUM_TBL, 'PRICE'[DATE] <= SLT_DAT)
VAR result = calculate(sum('PRICE'[PRICE]),SUM_TBL, 'PRICE'[DATE] = PRC_DAT)
return result
Thank you for your response.
I have corrected the measurements as you said.
But unfortunately, it didn't work.
