Max date and value by date filter slicer (dinamic)

[caslus]

The archive : https://transfernow.net/215t88x2cpeh

This is my table... and my filter:

If I put the filter 13/8/2019 to 5/09/2019... I want show just the yellow part and make a sum of those values 138.435,19 + 95.596,61of max date by filter:

15129   05/09/2019   138.435,19
15366  05/09/2019      95.596,61
-----     ------------    234.031,80

If i change the filter... 13/08/2019 to 26/09/2019

15129   26/09/2019   140.254,42

15366   13/08/2019     94.205,11
-----      -----------       234.459,53

-- Edit..

 Total in this case need be : 235.937,66

Thaks----------

Edit 09/01/2020

new archive : https://transfernow.net/115z99t4ires
I have/ I need

MY DATA-----------

My data

I NEED THIS RESPONSE BELOW

I NEED THIS RESPONSE

MY DATA-----------

My data

I NEED THIS RESPONSE BELOW

I NEED THIS RESPONSE

MY DATA-----------

My data

I NEED THIS RESPONSE BELOW

I NEED THIS RESPONSE

MY DATA-----------

My data

I NEED THIS RESPONSE BELOW

I NEED THIS RESPONSE

[amitchandak]

Why is the second visual you want to show both start and end date. In first time you want only end date.

Only max Date
var _max = max(date[date])
return
calculate(sum(table[total_cal]),table[date] = _max)

While you can use table date instead of date date. prefer calendar Date table.

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s.

Refer
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[caslus]

Thak you for response @amitchandak , but this not work.

Thake a look: Total in this case need be : 235.937,66

[Tad17]

I may be unclear on this, but if you are trying to find the date that matches your value for "Media 8 only max date" simply do a lookup function (using the format function where necessary) to look up the date just like you would with a Vlookup, then you can set that lookup measure as your filter parameter for the visual or the page.

[caslus]

Thank you @Tad17 , but I dont know how to do this. Could You show me ?

[amitchandak]

Check

https://www.dropbox.com/s/r3n0ubut0x1jf7e/help.pbix?dl=0

[caslus]

@Tad17 , the lookupvalue dont fix my problem.

@amitchandak , My version is older (Versão: 2.68.5432.841 64-bit (april de 2019)) than yours, I will need to try in home with my personal computer.

[amitchandak]

I created a calendar table joined with date and created following

Measure =

var _max=maxx('Date',('Date'[Date])) Return

CALCULATE(sum(data[VLR_SLD_TOT_CAL]),FILTER('Date','Date'[Date]=_max))

[caslus]

Unfortuly, this not work @amitchandak 
I this case the measure need be 374.199,59 becouse:

num_dpl	dat_ref	VLR_SLD_TOT_CAL
30495	05/09/2019 = max dat_REF(where num_dpl = 30495)	234.031,8
15129	25/09/2019= max dat_REF(where num_dpl =15129)	140.167,79
	TOTAL	374.199,59

num_dpl = 30495 have the last value in the last date (234.031,18) and

num_dpl = 30495 have the last value in the last date (234.031,18)

In your archive, if I change the date in slicer, dont show nothing 

[amitchandak]

Try

Measure 3 = 
VAR __id = MAX ( data[NUM_DPL] )
VAR __date = CALCULATE ( MAX( data[DAT_REF]), ALLSELECTED ( data ),  data[NUM_DPL] = __id ) 
RETURN CALCULATE ( MAX (data[VLR_SLD_TOT_CAL] ), VALUES ( data[NUM_DPL] ), data[NUM_DPL] = __id, data[DAT_REF] = __date )

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[caslus]

Dont work 😭

This mensure take just the max num_dpl. In this example I have 2 num_dpl, but in real case I have more than 1.000 distinct num_dpl.

@amitchandak I put new archive and new exemple.

[amitchandak · Accepted solution]

@caslus 

I got a new measure, It seems to give correct GT and correct values when view NUM_DPL. But when view by NUM_DOL and date , it does not show correct value, figuring out that

Total value = Calculate(
    sumx(  
        ADDCOLUMNS( 
            SUMMARIZE(
                data,
                data[NUM_DPL],
				"max_date",
				MAX( 'Date'[Date]),
                "Max_ID", max(data[NUM_DPL])
            ),
            "_num",
            calculate(
                Max(data[VLR_SLD_TOT_CAL]),
                filter(
                    (data),
                    data[NUM_DPL]= [Max_ID] && data[DAT_REF]= ([max_date]) )
                )
            )
        ,[_num]
    )
)

[caslus]

Perfect !!! @amitchandak  😀

Thank you so much!!!

[Tad17]

Refer to the tutorial here: https://docs.microsoft.com/en-us/dax/lookupvalue-function-dax

Syntax: LOOKUPVALUE( <result_columnName>, <search_columnName>, <search_value>[, <search_columnName>, <search_value>]…[, <alternateResult>])

For you it would look something like: Max Date = Lookupvalue([DAT_REF] , [VLR_SLD_TOT_CAL] , [Media 8 only max date])

You may have to include some paranthetical uses of the format function (tutorial here: https://docs.microsoft.com/en-us/dax/format-function-dax) because I do not know what format your data is in.

[amitchandak]

🤔, why only that row.  If possible please share a sample pbix file after removing sensitive information.Thanks.

My Recent Blog -

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[caslus]

Here is the archive @amitchandak 

https://transfernow.net/215t88x2cpeh