How to set default value for Date-From to begining date of current month?

[weerawan_m]

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?

[v-alq-msft · Accepted 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.

[weerawan_m]

Hi @v-alq-msft 

Thanks so much, K. Allan. 

Your answer is so benificial for us.