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

How to use nested select with multiple date range in Power BI?

Hi experts,

 

I would like to select customers that have made purchase in last year and made purchase again in this month. The SQL should be like:

 

select sales.cust_id 

from salesfact sales 

where sales.order_date between (1st date range)

and sales.cust_id in

(

select sales.cust_id 

from salesfact sales 

where sales.order_date between (2st date range)

)

 

In this example 2st date range should be last year and 1st date range should be this month. 

 

Both date range should be able to dynamically selected... so I think calculated column is not a good option...

I think I should add a dummy calender with inactive relationship to the sales table to use it as the 2st date range? Is there better options? 

 

Thank you in advance for any help.

2 REPLIES 2
Ashish_Mathur
Super User
Super User

Hi,

 

Please share some data and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-piga-msft
Resident Rockstar
Resident Rockstar

Hi @keajht31,

 

In Power BI Desktop, we could use SQL statement to get the conditional data source when you Get Data like below. 

get contional data.PNG

 

2. In addition, we could create query parameter in Query Editor for Date range. You could have a good reference of the blog below.

 

https://biinsight.com/power-bi-desktop-query-parameters-part-1/

 

3. We also could try with measures which may help you. If you want to use measures, please share some data and your desired output so that we could help further on it.

 

Best  Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.