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.

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 ❤️

  • sya's avatar
    sya
    Helper I
    4 years ago

    Hi Anonymous 

     

    It works splendidly with one-month selection. However when I selected multiple months it will show blank. Is there a way to make it show the latest available data? For example if i select jan, feb,mar, apr, it should show apr. If i select apr & may it should show apr.

    Sya