Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
sya
Helper I
Helper I

Return Latest Value Using Date Slicer

Hi All,

 

I have data up till April for 2022 and full data for 2021:

sya_0-1653355979734.png

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:

 

sya_1-1653356109947.png

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

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
JamesFr06_0-1653483506244.pngJamesFr06_1-1653483527417.png

 

View solution in original post

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

23 REPLIES 23
Ashish_Mathur
Super User
Super User

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi @Ashish_Mathur 

 

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

 

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

 

Sya

Hi,

This measure works

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

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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_0-1653533052952.png

Sya

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

I still need help sir.. :')

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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!

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

You are welcome.  Thank you for your kind words.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

Share the download link of your PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

 

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

 

Thanks

 

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
https://www.linkedin.com/in/excelenthusiasts/

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

 

Thank you.

 

Sya

Drag Measure instead of Measure2 in the card visual.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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
JamesFr06_0-1653483506244.pngJamesFr06_1-1653483527417.png

 

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_0-1653531225254.png

Sya

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.