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

[12Bowers12]

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] )

[AllisonKennedy · Accepted solution]

@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] )

[12Bowers12 · Accepted solution]

Thank you so much, Allison, it works perfectly. 

[AllisonKennedy · Accepted solution]

@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] )

[12Bowers12]

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

[AllisonKennedy]

@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).

[12Bowers12]

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