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,
I used the method like below to set up the slicer as default value - current month. https://community.powerbi.com/t5/Desktop/Setting-the-Default-Value-of-a-Slicer/td-p/16442
However, I also need to calculate YTD numbers and it doesn't work. please see below tables, is there any way to solve that? Thank you!
The YTD function is
Solved! Go to Solution.
Hi @vmao ,
You need to create a measure as below:
Measure = CALCULATE(SUM('Table'[#]),FILTER(ALL('Table'),'Table'[As Of Date]<=MAX('Table'[As Of Date])&&YEAR('Table'[As Of Date])=YEAR(MAX('Table'[As Of Date]))))
As in the column of "Current Month", the data type is text, not a date type,so when you use a time intelligence function, you need to add a filter,to set a date range to make it calculate in context.Otherwise ,it will return a value of the current row.
Hi @vmao ,
You need to create a measure as below:
Measure = CALCULATE(SUM('Table'[#]),FILTER(ALL('Table'),'Table'[As Of Date]<=MAX('Table'[As Of Date])&&YEAR('Table'[As Of Date])=YEAR(MAX('Table'[As Of Date]))))
As in the column of "Current Month", the data type is text, not a date type,so when you use a time intelligence function, you need to add a filter,to set a date range to make it calculate in context.Otherwise ,it will return a value of the current row.
Thank you!
The reason I am using 'current month' is because our data refreshed on a monthly basis, and I want viewer to always see the current month when they open the Power BI report. If I choose 'Jan 31, 2020' and when they open the report next month, the default value still shows Jan and they have to manually click to get Feb for the most recent date.
That is why the 'current month' method is chosen which also makes TOTALYTD function is unavilable.
If I drop 'current month' method, how could I make the most recent month as default selection choice in the report?
Details of the method see below:
https://community.powerbi.com/t5/Desktop/Setting-the-Default-Value-of-a-Slicer/td-p/16442
Hi @Anonymous,
No need to drop the 'current month' method,just correct your measure as I suggested in my last post.What I wrote is to tell you the reason why the measure you use doesnt work.
Hard to tell exactly as can't create a test model. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
However, I am guessing that your slicer is filtering your date table and hence TOTALYTD only has that date within its context or something along those lines. See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...
Thank you!
The reason I am using 'current month' is because our data refreshed on a monthly basis, and I want viewer to always see the current month when they open the Power BI report. If I choose 'Jan 31, 2020' and when they open the report next month, the default value still shows Jan and they have to manually click to get Feb for the most recent date.
That is why the 'current month' method is chosen which also makes TOTALYTD function is unavilable.
If I drop 'current month' method, how could I make the most recent month as default selection choice in the report?
Details of the method see below:
https://community.powerbi.com/t5/Desktop/Setting-the-Default-Value-of-a-Slicer/td-p/16442
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 |
---|---|
114 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |