Forum Discussion

Helper III

6 years ago

sql to dax

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

Community Champion

6 years ago

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
Helper III
6 years ago
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
  Community Champion
  6 years ago
  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
    Helper III
    6 years ago
    Correct me if I'm wrong
    
    I used INTERSECT Dax function with countRows
    
    Mariusz