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

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!

Ashish_Mathur's avatar
Ashish_Mathur
Super User
4 years ago

=eomonth(date(2022,max(Date[month]),1),0)

  • sya's avatar
    sya
    Helper I
    4 years ago

    thank you sir!! u r the best <3<3

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    4 years ago

    You are welcome.  Thank you for your kind words.

  • sya's avatar
    sya
    Helper I
    3 years ago

    Hello Sir Ashish_Mathur , 

     

    Now my data is updated with May 2022, June 2022 and July 2022 data.

    But when i set the month slicer to May, June, July, the DSO for 2022 remains at April 2022 figure which is 60.1. 

     

    It is supposed to show the latest figure, which means, if I selected Jan-July, DSO 2022 should show July 2022 figure.

     

    Please help, thank you

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    3 years ago

    Share the download link of your PBI file.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    3 years ago

    Hi,

    The file there is not in a .pbix format.  Please share the file which opens in PBI Desktop.

  • sya's avatar
    sya
    Helper I
    3 years ago

    Hi, I've reshared at the same link. Please check

     

    Thank you.

     

    Sya

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    3 years ago

    Drag Measure instead of Measure2 in the card visual.

  • sya's avatar
    sya
    Helper I
    3 years ago

    Oh gosh.. thank you sir. What would i do without you.

     

    If it is not too much, can you so kindly explain the mechanism of those two formulas..?

     

    Regards,

    Sya