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

[keajht31]

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.

[Ashish_Mathur]

Hi,

Please share some data and show the expected result.

[v-piga-msft]

Hi @keajht31,

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

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