cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper I

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

2 ACCEPTED SOLUTIONS
Anonymous
Not applicable

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

Super User

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
23 REPLIES 23
Super User

Hi,

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper I

https://1drv.ms/u/s!AjXpGcOFDGB2iGNO3xXRGtWnT-LQ

Sya

Super User

Hi,

This measure works

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

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper I

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

Super User

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper I

I still need help sir.. :')

Super User

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper I

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!

Super User

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

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper I

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

Super User

You are welcome.  Thank you for your kind words.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper I

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.

Super User

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper I

Hi, Below is the link. Let me know if you have trouble accessing it

Thanks

Super User

Hi,

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

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper I

Thank you.

Sya

Super User

Drag Measure instead of Measure2 in the card visual.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper I

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

Anonymous
Not applicable

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

Helper I

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