Forum Discussion

RL_171's avatar
RL_171
Frequent Visitor
3 years ago
Solved

How to create a parameter with dynamic Date query list

I want to create a Power BI report for sales forecasting. The report should present opportunities whose Date is equal to current month and future months in 12 month max.  Suppose the oppotunity table...
Need Help
  • KNP's avatar
    KNP
    3 years ago

    We're getting there ğŸ˜„

    You can't compare date and datetime types.

     

    Change the StartOfCurrentMonth query to...

    // StartOfCurrentMonth
let
    Source = Date.StartOfMonth(
        DateTime.FixedLocalNow()
    )
in
    Source

     

RL_171's avatar
RL_171
Frequent Visitor
3 years ago

I have another idea:

 

1. Add a column in the source data table to calculate the difference between Today and the "State of Month" date

2. Create a query in Advance Query Editor: if the days difference is equal to or greater than 0, load the rows. (I dont know how to write this query).

 

This way the PBI report loads the filtered data only. Does it make sense? KNP 

KNP's avatar
KNP
Super User
3 years ago

Ok, if you need to filter the data for performance reasons and you don't need the historical data for any other visuals, it's easy enough.

See attached (amended) PBIX file.

Here's the code if you prefer...

Create a new blank query and paste this code in the advanced editor...

// StartOfCurrentMonth
let
    Source = Date.StartOfMonth(
        DateTime.Date(
            DateTime.FixedLocalNow()
        )
    )
in
    Source

 

Main code (look at the last step that does the filtering)...

// data
let
    Source = {
        Number.From(
            #date(
                2021,
                1,
                1
            )
        )..Number.From(
            #date(
                2023,
                12,
                31
            )
        )
    },
    #"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"
            }
        }
    ),
    #"Added Custom" = Table.AddColumn(
        #"Renamed Columns",
        "value",
        each
            Number.RandomBetween(
                1257,
                15663
            )
    ),
    #"Inserted Start of Month" = Table.AddColumn(
        #"Added Custom",
        "Start of Month",
        each
            Date.StartOfMonth(
                [
                    Date
                ]
            ),
        type date
    ),
    #"Grouped Rows" = Table.Group(
        #"Inserted Start of Month",
        {
            "Start of Month"
        },
        {
            {
                "value",
                each
                    List.Sum(
                        [
                            value
                        ]
                    ),
                type nullable number
            }
        }
    ),
    #"Changed Type2" = Table.TransformColumnTypes(
        #"Grouped Rows",
        {
            {
                "value",
                Currency.Type
            }
        }
    ),
    #"Filtered Rows" = Table.SelectRows(
        #"Changed Type2",
        each
            [
                Start of Month
            ] >= StartOfCurrentMonth
    )
in
    #"Filtered Rows"

 

 

  • RL_171's avatar
    RL_171
    Frequent Visitor
    3 years ago

    I am trying to paste the code to the advanced editor, but can't figure out where did I go wrong:

    let
    Source = CommonDataService.Database("XXX.com"),
    dbo_lvs_opportunityforecast = Source{[Schema="dbo",Item="lvs_opportunityforecast"]}[Data]
    dbo_lvs_opportunityforecast = Date.StartOfMonth(
    DateTime.Date(
    DateTime.FixedLocalNow()
    )
    )
    in
    dbo_lvs_opportunityforecast

    • KNP's avatar
      KNP
      Super User
      3 years ago

      Did you do this step first?

       

      If you have, you should be able to do something like...

      (replace placeholder with your date column name)

      let
    Source = CommonDataService.Database(
        "XXX.com"
    ),
    dbo_lvs_opportunityforecast = Source{
        [
            Schema = "dbo",
            Item = "lvs_opportunityforecast"
        ]
    }[
        Data
    ],
    #"Filtered Rows" = Table.SelectRows(
        dbo_lvs_opportunityforecast,
        each
            [
                ReplaceThisWithTheNameOfYourDateColumn
            ] >= StartOfCurrentMonth
    )
in
    #"Filtered Rows"

       

       

      • RL_171's avatar
        RL_171
        Frequent Visitor
        3 years ago

        I renamed Query1 and unchecked Enable Load, it shows a new error:

         

         

  • RL_171's avatar
    RL_171
    Frequent Visitor
    3 years ago

    I created a Query like this:

     

    And then copy the following code to the Advanced Editor

     

    let
    Source = CommonDataService.Database(
    "XXX.com"
    ),
    dbo_lvs_opportunityforecast = Source{
    [
    Schema = "dbo",
    Item = "lvs_opportunityforecast"
    ]
    }[
    Data
    ],
    #"Filtered Rows" = Table.SelectRows(
    dbo_lvs_opportunityforecast,
    each
    [
    lvs_date
    ] >= StartOfCurrentMonth
    )
    in
    #"Filtered Rows"

     

    error message says: Expression.Error: The name 'StartOfCurrentMonth' wasn't recognized

    • KNP's avatar
      KNP
      Super User
      3 years ago

      Rename 'Query1' to 'StartOfCurrentMonth'.

      (also, you can right click on this query and untick Enable Load)