Differences in using dates from FACT TABLE and DIMENSION TABLE

[inmingoon]

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 : SYMBOL NAME

1 and 2 are fact tables.
3 and 4 are dimension tables.

The fact table and dimension table are connected through the DATE and SYMBOL columns.
The relationships are shown in the attached figure.

And I created a measure called PRICE.
Its purpose is to get the price from the PRICE table on the most recent date from the DATA table (there are 2 SYMBOLs).

PRICE =

VAR SLT_DAT = max('DATA'[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

This measure works well if I construct the model like in #1 (PUT DATE from dimension TBL).
However, it doesn't work if I configure it like number 2 (PUT DATE from fact TBL).

How can I modify it to create a measure that works well in Configuration 2?
The reason I want to make it like configuration #2 is that I think it's preferable to use DATE from the DATE table for the time intelligence to work well.

I have attached a picture and a pbix file.

https://drive.google.com/file/d/1SjZPMxy87bnu0O6IfFDKBPb4ES5K75yf/view?usp=sharing

Thank you very much for your time and consideration.

[Ashish_Mathur · Accepted solution]

Hi,

Please find attached the PBI file.

Hope this helps.

[parry2k · Accepted solution]

@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 

[parry2k · Accepted solution]

@inmingoon 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 

[parry2k · Accepted solution]

@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 

[inmingoon]

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.

[Ashish_Mathur · Accepted solution]

Hi,

Please find attached the PBI file.

Hope this helps.

[inmingoon]

Thank you so much for your help.

You applied the "calculate" formula in the row context.
I'm afraid that might demand a lot of resources on a large fact table.
So I'm asking if there is another way to implement it without context transition.

Actually, I'm frustrated because I don't understand why the measure I've created works well in picture #1 but not in picture #2.

Thanks again for your comments.

[parry2k]

@inmingoon your ask is a bit confusing. You mentioned you want to use the date from the date table, which makes sense, and then you said using the date from the date table is working fine. What we are solving here?

This measure works well if I construct the model like in #1 (PUT DATE from dimension TBL).
However, it doesn't work if I configure it like number 2 (PUT DATE from fact TBL).

How can I modify it to create a measure that works well in Configuration 2?
The reason I want to make it like configuration #2 is that I think it's preferable to use DATE from the DATE table for the time intelligence to work well.

[inmingoon]

Thank you for your reply.

What I mean is that if I place "the date" in the right table on the visualization from the "DATA" (fact) table, the measure works fine.(Fig.1) But if I place "the date" from the "DATE" (dimension) table, the measure doesn't work.(Fig.2, some rows have empty PRICE column.) Please look closely at the figure.

In order to properly implement the time intelligence, I believe that "the date" should come from the "DATE" table, so I asked for help on how to modify the measure so that Figure 2 works.

[Greg_Deckler]

@inmingoon 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

[inmingoon]

Thank you for your response.
I have corrected the measurements as you said.
But unfortunately, it didn't work.