Data Parameter: Last day of Previous month

[CathS]

Hi,

I have below code for a Data Cube:

let
    Source = SapHana.Database("TOOL-HANA-DB2:30015", [Implementation="2.0"]),
    Contents = Source{[Name="Contents"]}[Data],
    sap.sbotts.fin.mgmt = Contents{[Name="sap.sbotts.fin.mgmt"]}[Data],
    GeneralLedgerAccountQuery1 = sap.sbotts.fin.mgmt{[Name="GeneralLedgerAccountQuery"]}[Data],
    #"Added Items" = Cube.Transform(GeneralLedgerAccountQuery1,
        {
            {Cube.ApplyParameter, "P_AddVoucher", {"N"}},
            {Cube.ApplyParameter, "P_FromDate", {#date(2021, 9, 1)}},
            {Cube.ApplyParameter, "P_ToDate", {#date(2021, 9, 30)}},
            {Cube.AddAndExpandDimensionColumn, "[GeneralLedgerAccountQuery]", {"[AccountCode].[AccountCode].Attribute", "[AccountName].[AccountName].Attribute", "[ParentAccountCode].[ParentAccountCode].Attribute", "[ParentAccountName].[ParentAccountName].Attribute", "[SegmentationAccountCode].[SegmentationAccountCode].Attribute"}, {"Account Code", "Account Name", "Parent Account Code", "Parent Account Name", "Segmentation Account Code"}},
            {Cube.AddMeasureColumn, "Closing Balance (LC)", "[Measures].[ClosingBalanceLC]"},
            {Cube.AddMeasureColumn, "Opening Balance (LC)", "[Measures].[OpeningBalanceLC]"}
        }))
in
    #"Added Items" 

The current parameters require a start date and end date.

Is there a way to change the parameter to formula or set the dynamic parameters as below: 

FromDate = first day of previous month

ToDate = last day of previous month (or as of today if possible)

Thank you!

[DataInsights · Accepted solution]

@CathS,

Try these M expressions:

Today:

DateTime.Date(DateTime.LocalNow())

First Day of Previous Month:

Date.StartOfMonth(Date.AddMonths([Today], -1))

Last Day of Previous Month:

Date.EndOfMonth(Date.AddMonths([Today], -1))

These are shown as separate columns for training purposes, but can be written as follows:

First Day of Previous Month:

Date.StartOfMonth(Date.AddMonths(DateTime.Date(DateTime.LocalNow()), -1))

Last Day of Previous Month:

Date.EndOfMonth(Date.AddMonths(DateTime.Date(DateTime.LocalNow()), -1))