Getting value from latest given time each day

[Anonymous]

Hi Experts,

I need your help. I would like to create a measure to get the last checked-in value for each day.  The data model and expected output is shown below. Please help

Thanks

[v-xicai · Accepted solution]

Hi @Anonymous ,

You may keep the Cross filter direction of relationships between the tables above in Both, then you can create measure like DAX below.

Measure =

Var d=CALCULATE(MAX('Fact_Tourist arrived'[TimeKey]),ALLEXCEPT('Fact_Tourist arrived','Fact_Tourist arrived'[Hotel Key],'Fact_Tourist arrived'[DateKey]))

Return

CALCULATE(SUM('Fact_Tourist arrived'[No. of Tourist arrived]),FILTER(ALLSELECTED('Fact_Tourist arrived'),'Fact_Tourist arrived'[TimeKey]=d&&'Fact_Tourist arrived'[Hotel Key]=MAX('Fact_Tourist arrived'[Hotel Key])&&'Fact_Tourist arrived'[DateKey]=MAX('Fact_Tourist arrived'[DateKey])))

Best Regards,

Amy 

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks @Amy
It worked.

[amitchandak]

@Anonymous 

Try like

Measure =
VAR __id = MIN ( 'Table'[Datetime] )
VAR __date = CALCULATE ( MIN( 'Table'[Datetime] ), ALLSELECTED ( 'Table' ), 'Table'[hotelname] = __id )
RETURN CALCULATE ( sum ( 'Table'[tourist arrived] ), VALUES ( 'Table'[hotelname] ),values('Date'[Date]), 'Table'[hotelname] = __id, 'Table'[Datetime] = __date )

If does not work share some sample data

[Mariusz]

Hi @Anonymous 

Try this,

Measure = 
VAR __maxTime = 
    GROUPBY( 
        'Table', 
        'Hotel'[Hotel Name],
        'Date'[Date], 
        "@maxTime", MAXX( CURRENTGROUP(), 'Table'[Time] )
    )
RETURN 
CALCULATE(
    SUM( 'Table'[No of Tourist ] ),
    TREATAS( __maxTime, 'Hotel'[Hotel Name], 'Date'[Date], 'Table'[Time] )
)

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn