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.
good morning, I currently have a report that contains 2 slicer, one associated with years and another with months, I want to leave pordefecto every time it is opened and report are selected automatically
Solved! Go to Solution.
Hi @Syndicate_Admin ,
Maybe you can try the function ISFILTERED().
IF no selection, then get MONTH of TODAY() and use CALCULATE() to sum the value of this month.
You can refer the following expression:
Total =
VAR _today =
TODAY()
VAR _Defult =
CALCULATE(
SUM( 'DATE'[Value] ),
FILTER( ALL( 'DATE' ), MONTH( [DATE] ) = MONTH( TODAY() ) )
)
RETURN
IF( ISFILTERED( 'DATE'[Month] ), SUM( 'DATE'[Value] ), _Defult )
The result:
I put the pbix file in the end you can refer.
Best Regards
Community Support Team _ chenwu zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Syndicate_Admin ,
Maybe you can try the function ISFILTERED().
IF no selection, then get MONTH of TODAY() and use CALCULATE() to sum the value of this month.
You can refer the following expression:
Total =
VAR _today =
TODAY()
VAR _Defult =
CALCULATE(
SUM( 'DATE'[Value] ),
FILTER( ALL( 'DATE' ), MONTH( [DATE] ) = MONTH( TODAY() ) )
)
RETURN
IF( ISFILTERED( 'DATE'[Month] ), SUM( 'DATE'[Value] ), _Defult )
The result:
I put the pbix file in the end you can refer.
Best Regards
Community Support Team _ chenwu zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@mancemo You can select a value in the slicers and they will be the same values you see each time you open the report.
Can you provide more detail on what you need though? Do you want the month to be dynamic and always default to current month? You may find Relative Date slicer more helpful than separate month and year slicers:
https://docs.microsoft.com/en-us/power-bi/visuals/desktop-slicer-filter-date-range
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, what I want is that when the report is opened the slicers that are showing date and year are default to the current feha and update the values of the report
the panel is this
and that when it opens automatically, year and month are selected to update the other indicators
It's not possible to set the default of your current slicer layout to current month. You could create a calculated column in Power Query that for example for this month of November would have the values:
Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct
Current
Dec
And do the same for the Year. That way you could set the default value of both slicers to 'Current' and then the use can choose a different month if they desire.
To do this, click Transform Data to open Power Query.
Click Add Column tab in the ribbon, then click Custom Column and use this code:
if [Month name] = Date.MonthName(DateTime.LocalNow()) then "Current" else [Month name]
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 thanks, I tell you a little that the date table I get from the following query
Sorry for not being clear - the formula I gave is M (not DAX) so you need to click Transform Data to add this in Power Query. Since your Date table uses DAX, you will need to create the Date table in Power Query to use this method: https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html
Alternative option is to use DAX to create the column, but it's less efficient.
New Column =
VAR _CurrentMonth = MONTH(TODAY())
RETURN
IF( _CurrentMonth = Date[Month], "Current", Date[Month])
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
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 |
---|---|
109 | |
96 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |