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 has two columns: Date and Estimated Revenue.  The data source has both historical and future data -- "Date" column includes past date and future date. The Forecast Report should start from 1st day of the current month) and end to 12 month after current month as shown in the following sample table.

 

DateEstimated Revenue
2022/10/01$8000
2022/11/01$6000
2022/12/01$1500
2023/01/01$600
2023/02/01$1200
2023/03/01$6000
2023/04/01$5000
2023/05/01$400
2023/06/01$6500
2023/07/01$7000
2023/08/01$5600
2023/09/01$800

 

I think I need to 1)create a list which can propulate the 12 dynamic dates starting from current month, 2) create a parameter to filter source data by the list query. How can I achieve this? I am new to Power BI and has no SQL code and little DAX experience.

 

Thanks in advance.

 

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

     

13 Replies

  • KNP's avatar
    KNP
    Super User
    3 years ago

    Hi RL_171,

     

    I think it'll be pretty easy to solve but I need a little more detail.

    Can you post some sample data from the source system? (obfuscate anything sensitive)

    Is the source data at the same grain e.g. is it daily or monthly?

    Do you have a date table in your model?

     

     

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