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 data.

For example, if i filter Jan-Apr, 2022 card will show 24, 2021 will show 77:

 

Now my problem is when I choose Dec, it will show blank for 2022. I want it to show the last month's available data. So in this case, 2022 DSO should show 24 when I choose any month from May-Dec.

 

Please help :')))

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.

23 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    sya , the table should have a date or sort month order

     

    example measure

    new measure =
    var _max = maxx(allselected(Date),Date[Date])
    return
    calculate( sum(Table[Value]), filter('Date',eomonth( 'Date'[Date],0) =eomonth(_max,0) ))

    • sya's avatar
      sya
      Helper I
      4 years ago

      Hi amitchandak ,

       

      The card still returns blank when i applied your formula. (p/s: matrix table is only used as reference, i have set the interaction for the date slicer to have no effect on matrix)

       

      Sya