Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello! I am looking to create a measure that calculates the difference between the count of deals closed this month and count of deals closed last month.
To get the count of deals, I am getting the distinct count of IDs.
I have a completed date field which is the date data type.
Thank you very much!
Hi,
Create a Calendar Table with calculated columns formulas for extracting Year, Month name and Month number. Sort the Month name column by the Month number column. Create a relationship from the Date column of the Data Table to the Date column of the Calendar Table. To your Table/matrix visual, drag Year and Month name from the Calendar Table. Write these measures
Deals closed = distinctcount(Data[ID])
Deals closed in previous month = calculate([Deals closed],previousmonth(Calendar[Date]))
Diff = [Deals closed]-[Deals closed in previous month]
Hope this helps.
@kristenmcnelly Maybe:
Measure =
VAR __Today = TODAY()
VAR __TMStart = DATE(YEAR(__Today),MONTH(__Today),1)
VAR __LMEnd = EOMONTH(__Today,-1)
VAR __LMStart = DATE(YEAR(__LMEnd),MONTH(__LMEnd),1)
VAR __TM = COUNTROWS(DISTINCT(SELECTCOLUMNS(FILTER('Table',[Date] <= __Today && [Date] >= __TMStart),"ID",[ID])))
VAR __LM = COUNTROWS(DISTINCT(SELECTCOLUMNS(FILTER('Table',[Date] <= __LMEnd && [Date] >= __LMStart ),"ID",[ID])))
RETURN
__TM - __LM
User | Count |
---|---|
130 | |
108 | |
101 | |
67 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |