Return Latest Value Using Date Slicer

[sya]

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 :')))

[Anonymous · Accepted solution]

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]

Hi,

Share the link from where i can download your PBI file.

[sya]

Hi @Ashish_Mathur 

Please find the attached link. Thank you so much sir.

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

Sya

[Ashish_Mathur]

Hi,

This measure works

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

Hope this helps.

[sya]

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]

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

[sya]

I still need help sir.. :')

[Ashish_Mathur · Accepted solution]

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]

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]

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

[sya]

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

[Ashish_Mathur]

You are welcome.  Thank you for your kind words.

[sya]

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]

Share the download link of your PBI file.

[sya]

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

https://drive.google.com/drive/folders/1ycc45tQdHk0CqPamVchdSyw8sYB6Qf4I?usp=sharing

Thanks

[Ashish_Mathur]

Hi,

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

[sya]

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

Thank you.

Sya

[Ashish_Mathur]

Drag Measure instead of Measure2 in the card visual.

[sya]

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 · Accepted solution]

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]

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