Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
select distinctcount(*) from table where Date<' date' andDate>' date 'and customers id in (select Customer Id from table where date<'date' and date>' date ')
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
Hi @manideep547
Can you provide a data sample and snap of an expected outcome?
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
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 )
)
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |