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
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 result
Thank you very much for your solution.
The code you created works perfectly.
It is elegant and concise, which is good.
That was exactly what I was looking for.
I don't fully understand your solution, but I will study it.Both solutions work well.
Thank you very much.
