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

     

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)

     

    • 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