DAX Exp to get last updated value based on selected filter date

[Anonymous]

Hi Team,

Please help me out on below.

I am trying to get the DAX expression for high limit value in table based on filter date. I am having a date filter. I am having below table with date, values and tagid. Tagvalues table.

Date	TAGID	VALUE
10-06-2018	1	30
10-06-2018	2	40
20-11-2018	1	19
20-11-2018	2	29
10-11-2018	1	25
10-11-2018	2	13
01-12-2018	2	56
01-12-2018	1	36
01-11-2018	2	60

Requirement: 

If we select the date filter with date 05th Dec 2018 I need the value 56 of date 01 Dec 2018 with tagid 2 value as this is the latest updated highest limit value.

If we select the date filter as 01 Oct 2018 I need a value 40 of date 10 Jun 2018 as this is the latest value before our date selection. Below is the sql query to understand. 

If we select the date filter as 10 Jun 2018 I need a value 40 of date 10 Jun 2018 as this is the latest value before or equal to our date selection. Below is the sql query to understand. 

Select top 1 value from  tagvalues where tagid = 2 and date <= selecteddate order by date desc.

Please help  me out to get the highest limit value based on the above data I am new to power BI dax expressions.

Thanks and Regards,

Dathy

[Anonymous]

Dax measure needed to get value based on max date in slicer date filter applied.
Sales should get sum based on date range applied in slicer filter but the Value should only come based on max date and matching to ID from the row

Thanks in Advance

[v-yulgu-msft · Accepted solution]

Hi @Anonymous,

Please create a measure as below:

Measure 2 =
VAR maxdate =
    CALCULATE (
        MAX ( Tagvalue[Date] ),
        FILTER (
            ALL ( Tagvalue ),
            Tagvalue[Date] <= SELECTEDVALUE ( date_master[dt_date] )
        )
    )
RETURN
    CALCULATE (
        MAX ( Tagvalue[VALUE] ),
        FILTER ( ALL ( Tagvalue ), Tagvalue[TAGID] = 2 && Tagvalue[Date] = maxdate )
    )

 

Best regards,

Yuliana Gu

[AlB]

Hi @Anonymous

How about

Measure =
CALCULATE (
    MAX ( Tagvalues[value] );
    FILTER ( ALL ( 'Date' ); 'Date'[Date] <= MAX ( 'Date'[Date] ) )
)

where 'Date'is your date table?

[Anonymous]

Sorry this expression is not working I am selecting date from date_master[dt_date] table in filter and comparing with date in Tagvalues table I need to check both the filters date_master[dt_date] is less then latest Tagvalue[date] and get the max limit(tagid = 2 ) for higest limit.

Thanks,

Vijay D

[AlB]

@Anonymous

You want it always for tagid =2? It would more convenient to have the tagid in a slicer but give this a try. You'll have to adapt the it with the name of your Date table.

Measure =
VAR LatestDate =
    CALCULATE (
        MAX ( Tagvalues[Date] );
        FILTER ( ALL ( 'Date' ); 'Date'[Date] <= MAX ( 'Date'[Date] ) );
        Tagvalues[Tagid] = 2
    )
RETURN
    LOOKUPVALUE (
        Tagvalues[value];
        Tagvalues[Date]; LatestDate;
        Tagvalues[Tagid]; 2
    )