Rolling count with filters on start date and end date

[JackWren]

Hi everyone, 

I am trying to see if I can transpose this SQL query into PowerBI. 

Basically, I have a count that I want to make month by month but the trick is to excluse certain values based on a start date and a end date for each month. 

For the start date, I want to count everything that comes before the end of the month (including previous months). 

For the end date, I want to count everything that comes after the end of the month (including next months). 

Example in SQL : 

SEL   customer_id,        '

202101' _PERIOD,      

 START_DT,                    

 END_DT,       

 count(distinct products)       

FROM    Table 1      

Where (assign_start_dt and <=1210131 end_dt >= 1210131)       

I started something in dax for the start date but when it comes to the end date, I don't know how to proceed. 

I have a date calendar and I tried something with userelationship to no avail. 

Quantity startdate =

Calculate(

distinctcount(Query1[products]),

FILTER(

all('DWH Calender'[DATES]),

'DWH Calender'[DATES]<= MAX('DWH Calender'[MONTH_END_DT])))

Thanks

Jack

[lbendlin]

Your SQL query is missing the "group by" part and there are typos in the "where" clause.

Please show your data model. Do you have an active and inactive relationship between the calendar table and the Query1 table?

You can't use MAX inside CALCULATE's filter list. Use variables to prevent filter context impact.

[JackWren]

I may have been unclear.  I am not trying to pass the query as it is in Powerbi

I am trying to filter on the dates in DAX and not in SQL. 

So here is the query that I have actually used. The where clause works fine. 

SEL distinct
cust_id,
START_DT,
END_DT,
COUNT(distinct na) as NA
FROM Table 1
WHERE (contr_type IN ('INT','FIT' ) OR ADSL_PRODUCT_IND = 1)
group by 1,2,3

Here is my data model. I do have an active and an inactive relationship between the calendar table and the query1 table

[JackWren · Accepted solution]

I found a solution based on SQLBI's info

Analyzing events with a duration in DAX - SQLBI