Forum Discussion
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 ')
8 Replies
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.
LinkedInselect 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
amitchandakHi 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
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
