Forum Discussion

sya's avatar
sya
Helper I
4 years ago
Solved

Return Latest Value Using Date Slicer

Hi All,   I have data up till April for 2022 and full data for 2021: What I am trying to achieve is when I filter to multiple months, the card will show only the latest month's available dat...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi sya,

     

    Please try this code. it works here with the provided pwbi

    new measure =

    VAR yearmax =
    CALCULATE ( MAX ( 'Date'[Year] ), REMOVEFILTERS ( 'Date' ) )
    VAR monthselect =
    SELECTEDVALUE ( 'Date'[Month Name] )
    var monthnumb=CALCULATE(max('Date'[Month]),'Date'[Month Name]=monthselect)
    VAR _Datemax =
    date(yearmax,monthnumb,1)
    VAR _Maxdatedsoalldates =
    CALCULATE ( MAX ( Sheet1[Month] ), REMOVEFILTERS ( 'Date' ) )
    VAR _Maxdatedso =
    CALCULATE ( MAX ( Sheet1[Month] ),'Date'[Year]=yearmax)
    var dsomaxperiod=CALCULATE ( [DSO Allocated],Sheet1[Month]=_Maxdatedsoalldates)
    VAR result =
    if(ISBLANK(_Maxdatedso),
    CALCULATE ( [DSO Allocated],Sheet1[Month]=_Maxdatedsoalldates,REMOVEFILTERS('Date'[Month Name])),IF (_Maxdatedso <= _Datemax,
    CALCULATE ( [DSO Allocated],'Date'[Year]=yearmax)))
    RETURN
    result

     

  • Ashish_Mathur's avatar
    Ashish_Mathur
    4 years ago

    Hi,

    Try these measures

    Measure = CALCULATE([DSO Allocated],LASTNONBLANK('Date'[Date],CALCULATE([DSO Allocated])))
    Measure 2 = maxx(summarize(calculatetable('Date',datesbetween('Date'[Date],date(2022,1,1),[Last date of selected month])),'Date'[Year],'Date'[Month Name],"ABCD",[Measure]),[ABCD])

    Hope this helps.

Ashish_Mathur's avatar
Ashish_Mathur
Super User
4 years ago

Hi,

Share the link from where i can download your PBI file.

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      4 years ago

      Hi,

      This measure works

      Measure = CALCULATE([DSO Allocated],LASTNONBLANK('Date'[Date],CALCULATE([DSO Allocated])))

      Hope this helps.

      • sya's avatar
        sya
        Helper I
        4 years ago

        Hi Ashish_Mathur 

         

        It works with multiple selection but not with single selection.. When I choose May/Jun/Jul/Aug/Sep/Oct/Nov/Dec it should show Apr 

         

        Sya

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi sya,

       

      Please try this code. it works here with the provided pwbi

      new measure =

      VAR yearmax =
      CALCULATE ( MAX ( 'Date'[Year] ), REMOVEFILTERS ( 'Date' ) )
      VAR monthselect =
      SELECTEDVALUE ( 'Date'[Month Name] )
      var monthnumb=CALCULATE(max('Date'[Month]),'Date'[Month Name]=monthselect)
      VAR _Datemax =
      date(yearmax,monthnumb,1)
      VAR _Maxdatedsoalldates =
      CALCULATE ( MAX ( Sheet1[Month] ), REMOVEFILTERS ( 'Date' ) )
      VAR _Maxdatedso =
      CALCULATE ( MAX ( Sheet1[Month] ),'Date'[Year]=yearmax)
      var dsomaxperiod=CALCULATE ( [DSO Allocated],Sheet1[Month]=_Maxdatedsoalldates)
      VAR result =
      if(ISBLANK(_Maxdatedso),
      CALCULATE ( [DSO Allocated],Sheet1[Month]=_Maxdatedsoalldates,REMOVEFILTERS('Date'[Month Name])),IF (_Maxdatedso <= _Datemax,
      CALCULATE ( [DSO Allocated],'Date'[Year]=yearmax)))
      RETURN
      result

       

      • sya's avatar
        sya
        Helper I
        4 years ago

        Hi Anonymous 

         

        Thank you so much! You are awesome ❤️