Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello,
I'm going to explain my problem.
I've got three different tables:
1) Table A : DATE, LABEL, CLIENT_ID, DISTINCTCOUNT(CLIENT_A) (FILTERED TABLE BASED ON SQL QUERY)
2) Table B : START, END, LABEL, CLIENT_B (TOTAL TABLE)
3) Calendar = CALENDAR(MAX(TABLE_A(DATE)), MIN(TABLE_A(DATE))
I've to compute a measure like this : % CLIENT = DISTINCTCOUNT(CLIENT_A) / DISTINTCOUNT(CLIENT_B)
and insert this measure in a graph with month(DATE) on X-axis and % Client on Y-axis filtered by CLIENT of TABLE A.
So my denominator must follow the DATE on X-axis, for example if the month is JAN the START WOULD BE >= 1 JAN and END WOULD BE <= 31 JAN and so on for each month.
This is my dax:
First month of year of selected date
CALCULATE(DISTINTCOUNT(TableB[CLIENT_B]) ,DATESMTD(dateadd('Date'[Date],(-1*month(Today())_ +1 ,MONTH)))
First Month This year Today =
var _min = eomonth(today(),-1*month(Today()))+1
var _max = eomonth(today(),-1*month(Today())+1)
return CALCULATE(DISTINTCOUNT(TableB[CLIENT_B]), FILTER('Date','Date'[Date] >=_min && 'Date'[Date] <= _max))
Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510
Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s
User | Count |
---|---|
98 | |
90 | |
82 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
67 |