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.

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

Ashish_Mathur's avatar
Ashish_Mathur
Super User
4 years ago

Has your question already been answered by James or do you still want help?

  • sya's avatar
    sya
    Helper I
    4 years ago

    I still need help sir.. :')

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      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.

      • sya's avatar
        sya
        Helper I
        4 years ago

        Hello again sir Ashish_Mathur 

         

        Can i have the formula for measure  [Last date of selected month] that u used in Measure 2?

         

        Thank you very much!