Forum Discussion

caslus's avatar
caslus
Icon for Helper I rankHelper I
6 years ago
Solved

Max date and value by date filter slicer (dinamic)

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 thos...
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • amitchandak's avatar
    amitchandak
    6 years ago

    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]
    )
)
Anonymous's avatar
Anonymous
Not applicable
6 years ago

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's avatar
caslus
Icon for Helper I rankHelper I
6 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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.

    • caslus's avatar
      caslus
      Icon for Helper I rankHelper I
      6 years ago

      Anonymous , 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's avatar
        amitchandak
        Icon for Super User rankSuper User
        6 years ago

        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))