Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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] )
Solved! Go to 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] )
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
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 so much, Allison, it works perfectly.
@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] )
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
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
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).
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
101 | |
68 | |
68 | |
43 |
User | Count |
---|---|
145 | |
106 | |
105 | |
90 | |
65 |