sql to dax

[manideep547]

select distinctcount(*) from table where Date<' date' andDate>' date 'and customers id in (select Customer Id from table where date<'date' and date>'  date ')

[amitchandak]

Assume you have a date slicer from date table that is joined to your table/fact

Measure =

var _min=minx(Date,Date[Date])

var _max=maxx(Date,Date[Date])

Return

calculate(distinctcount(Table[field),Filter(All(Date), Date[Date]<=_max && Date[Date]>=_min))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s.

Refer
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi

https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on Linkedin

[Mariusz]

Hi @manideep547 

Can you provide a data sample and snap of an expected outcome?

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[manideep547]

select distinctcount(*) from table where Date<1/1/2018andDate>' 1/1/2018'and customers id in (select Customer Id from table where date<'1/1/2017' and date>' 1/12/2017')

if the customer has the transaction in between 1/1/2018 to 1/1/2019 and also having the transaction in between 1/1/2017 to 1/1/2017
@Mariusz 
@amitchandak 

[Mariusz]

Hi @manideep547 

Try this.

Measure = 
VAR __conditionOne = 
CALCULATETABLE(
    VALUES( 'Table'[Customer Id] ),
    ALL( 'Table'[Date] ),
    'Table'[Date] > DATE( 2017, 1, 1 ),
    'Table'[Date] < DATE( 2017, 12, 1 ) 
)
RETURN 
CALCULATE(
    DISTINCTCOUNT( 'Table'[Customer Id] ),
    TREATAS( __conditionOne, 'Table'[Customer Id] ),
    ALL( 'Table'[Date] ),
    'Table'[Date] > DATE( 2018, 1, 1 ), 
    'Table'[Date] < DATE( 2019, 1, 1 ) 
)

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[manideep547]

Include average=500  in the above query 
who has average transactions is 500  

select distinctcount(*) from table where Date<' date' andDate>' date 'and average(Amount)='500'and customers id in (select Customer Id from table where date<'date' and date>'  date 'and average(Amount)='500')
@Mariusz 

[manideep547]

Correct me if I'm wrong 

I used INTERSECT Dax function with countRows

@Mariusz 

[Mariusz]

Hi @manideep547 

INTERSECT will work as well.

Best Regards,
Mariusz

Please feel free to connect with me.
LinkedIn

[manideep547]

Thank you ...!!!
@Mariusz