Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
DoubleElle_2
Frequent Visitor

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 

 
 

1 REPLY 1
amitchandak
Super User
Super User

@DoubleElle_2 ,

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

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.