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.
Report has Date-From and Date-To as slicer.
Default value for Date-From : Begining date of current month
Default value for Date-To : Today-1
For Example :
Today is 27/05/2020
Date-From : 01/05/2020
Date-To : 26/05/2020
Now I can set default value for Date-To. The problem is Date-From
How to set default value for Date-From to begining date of current month?
Solved! Go to Solution.
Hi, @weerawan_m
It is unavailable to set a default vlaue for a slicer directly. However , you may create a calculated table or create a table in Power Query to generate a list of dates between 'Date-From' and ‘Date-to’. The pbix file is attached in the end.
Calculated table:
Table 2 = CALENDAR(DATE(YEAR(TODAY()),MONTH(TODAY()),1),TODAY()-1)
Power Query:
let
today = Date.From(DateTime.LocalNow()),
Source = List.Dates(Date.StartOfMonth(today),Date.Day(today)-1,#duration(1,0,0,0)),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Date"}})
in
#"Renamed Columns"
Then you may use the date column as a slicer to filter the result.
As a workaround, I created some sample date. You may create a column to mark if the date is between 'Date-From' and ‘Date-to’. 1 means it achieves the requirement while 0 means it doen't achieve the requirement.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtI31DcyMDJQitWJVjJG5pggc0zROAbIPGMUOSNzFJ4FlBcLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
Custom1 = Table.AddColumn(#"Changed Type","new",each if [Date]>=Date.StartOfMonth(Date.From(DateTime.LocalNow())) and [Date]<=Date.From(DateTime.LocalNow())-#duration(1,0,0,0) then 1 else 0)
in
Custom1
Finally you may use the visual level filter to display the result. Today is 5/28/2020.
For further information, you may refer the similar thread .
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @weerawan_m
It is unavailable to set a default vlaue for a slicer directly. However , you may create a calculated table or create a table in Power Query to generate a list of dates between 'Date-From' and ‘Date-to’. The pbix file is attached in the end.
Calculated table:
Table 2 = CALENDAR(DATE(YEAR(TODAY()),MONTH(TODAY()),1),TODAY()-1)
Power Query:
let
today = Date.From(DateTime.LocalNow()),
Source = List.Dates(Date.StartOfMonth(today),Date.Day(today)-1,#duration(1,0,0,0)),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Date"}})
in
#"Renamed Columns"
Then you may use the date column as a slicer to filter the result.
As a workaround, I created some sample date. You may create a column to mark if the date is between 'Date-From' and ‘Date-to’. 1 means it achieves the requirement while 0 means it doen't achieve the requirement.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtI31DcyMDJQitWJVjJG5pggc0zROAbIPGMUOSNzFJ4FlBcLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
Custom1 = Table.AddColumn(#"Changed Type","new",each if [Date]>=Date.StartOfMonth(Date.From(DateTime.LocalNow())) and [Date]<=Date.From(DateTime.LocalNow())-#duration(1,0,0,0) then 1 else 0)
in
Custom1
Finally you may use the visual level filter to display the result. Today is 5/28/2020.
For further information, you may refer the similar thread .
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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 |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |