Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Dax based on Calendar Date

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:

CountDistinctClientB=
VAR SelectedMonth = MAX(CALENDAR[DATE])
VAR StartMonth = DATE(YEAR(SelectedMonth), MONTH(SelectedMonth), 1)
VAR EndMonth = EOMONTH(SelectedMonth, 0)
VAR FilteredClient =
FILTER(
TABLE_B,
TABLE_B[START] >= StartMonth &&
TABLE_B[END] <= EndMonth
)
RETURN
CALCULATE(
COUNTROWS(SUMMARIZE(FilteredClient, TABLE_B[CLIENT_B])),
TABLE_B[LABEL] = SELECTEDVALUE(TABLE_A[LABEL])
)

Is there anyone who can help me please? 

I can't overcome this issue.

Thanks in advance 

 
 

Need Help

1 Reply

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    2 years ago

    Anonymous ,

    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