cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
12Bowers12
Resolver II
Resolver II

select a single date with SELECTVALUE when Filter Pane is selected [is on or before]

Hello, everyone,

I have a simple measure [Report Title] as shown below.

In the Filter pane, there is a page filter 'Date'[Date]. With Advanced Filtering, if the option of [Show Items When the Value] is chosen as [is], the measure [Report Title] will exactly show such as “Year To Date Loss as of 08/31/21”. However, the visual will only report the single day 08/31/21 loss value. The visual is expected to report the Year To Date loss value from 01/01/21 to 08/31/21.

 

If the option of [Show Items When the Value] is chosen as [is on or before], the visual shows the correct YTD amount, but the measure [Report Title] will only show “Year To Date Loss as of “ and will not show the date information.

Are there any way to show the Year To Date value in the visual and also show the date on the [Report Title] measure when “is on or before” is selected?

Appreciate your help.

Dennis

 

Report Title = "Year To Date Loss as of " & SELECTEDVALUE( ‘Date’[Date] )

 

 

2 ACCEPTED SOLUTIONS
AllisonKennedy
Super User
Super User

@12Bowers12  Use MAX or LASTDATE instead of selectedvalue (I suggest MAX as it returns a scalar while LASTDATE returns a table which you don't want)

 

Report Title = "Year To Date Loss as of " & MAX( ‘Date’[Date] )


Please @mention me in your reply if you want a response.

Check out my Tokyo 2020 report with live stats - KUDOS much appreciated

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

View solution in original post

12Bowers12
Resolver II
Resolver II

Thank you so much, Allison, it works perfectly. 

View solution in original post

5 REPLIES 5
12Bowers12
Resolver II
Resolver II

Thank you so much, Allison, it works perfectly. 

View solution in original post

AllisonKennedy
Super User
Super User

@12Bowers12  Use MAX or LASTDATE instead of selectedvalue (I suggest MAX as it returns a scalar while LASTDATE returns a table which you don't want)

 

Report Title = "Year To Date Loss as of " & MAX( ‘Date’[Date] )


Please @mention me in your reply if you want a response.

Check out my Tokyo 2020 report with live stats - KUDOS much appreciated

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

View solution in original post

Hello, Allison,
I have one more question about the page filter 'Date'[Date] on Filters pane.
The scheduled refresh has been setup on daily basis from clouds. Once the refresh is complete, is it possible for the date on the page filter 'Date'[Date] to automatically update to the refreshed date?
Right now I manually change the date on the page filter 'Date'[Date].
Appreciate your help.
Dennis

@12Bowers12 You could change it to Relative Date and do something like the last 10 years that you know encompasses all the data before today. That will be relative and update for each day the report is opened without requiring you to change the date. You can include this in addition to your existing filter, or instead of. Whichever works best for you, but if you do this in addition to your existing measure, your existing measure will overwrite the relative one (Power BI combines all filters to find most restrictive date range that's included in all filters).

 

AllisonKennedy_0-1633062724166.png

 


Please @mention me in your reply if you want a response.

Check out my Tokyo 2020 report with live stats - KUDOS much appreciated

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Thank you, Allison,

That's great. I just neet the data from 01/01/21 to current date ( for instance, as of now 10/04/21). If I choose 'is in the last 1 Year', I am not sure the date range it selects, 01/01/21-10/04/21 or 10/04/20-10/04/21? 

Sincerely,

Dennis

 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.