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

Thanks for responding to my question. The source data looks exact the same, i.e. in Date format but shows first date of each month. The only differenct between the source data and the PBI data is that the source data has more rows (past dates) while the PBI should show the current and future dates only. I have built a date table in PBI model and connected with the opportunity table like this:

Date =
ADDCOLUMNS(
    CALENDARAUTO(),
    "Year", Year([Date]),
    "Month", FORMAT([Date],"mmmm"),
    "Month Number", MONTH ([Date]),
    "Quarter", FORMAT([Date],"\QQ"),
    "Quarter Number", QUARTER([Date])
)  
KNP's avatar
KNP
Super User
3 years ago

Ok, based on that info, I think a measure like this could do what you want.

Result = 
var _today = TODAY()
var _year = YEAR(_today)
var _month = MONTH(_today)
var _futureMonths = 12
var _filter = 
FILTER(
    data, 
    data[Start of Month] >= DATE(_year,_month,1) && 
    data[Start of Month] <= DATE(_year,_month + _futureMonths,1) 
    )
RETURN
CALCULATE([_Value], _filter)

 

I've attached a sample PBIX for you to look at. It's a little dependent on the complete model.

Let me know how you get on.

(I'm not sure about the CALENDARAUTO and interaction with this, I don't use it. If you're going to build your date table with DAX, I'd recommend using CALENDAR)

 

  • 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"