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

Yes, magic happens! This is the result I want. Question: as there are multiple years of data saved in the database, when I open the PBI report, I just want to load the filtered data into the report. Can I apply this measure to parameter when transforming data?

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 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)