Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
RL_171
Frequent Visitor

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.

 

1 ACCEPTED SOLUTION

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

 

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x

View solution in original post

13 REPLIES 13
KNP
Super User
Super User

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?

 

 

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x
RL_171
Frequent Visitor

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

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)

 

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x
RL_171
Frequent Visitor

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
Frequent Visitor

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 

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"

 

 

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x
RL_171
Frequent Visitor

I created a Query like this:

RL_171_0-1664575162683.png

 

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

Rename 'Query1' to 'StartOfCurrentMonth'.

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

 

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x
RL_171
Frequent Visitor

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

Did you do this step first?

KNP_0-1664572986904.png

 

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"

 

 

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x
RL_171
Frequent Visitor

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

 

RL_171_1-1665548587122.png

 

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

 

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x
RL_171
Frequent Visitor

Bingo!! the query is working!! Thank you so much for sharing and being patience with me 😃

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors