Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi everyone,
I want to create a formula which count how many times a specific value shows up in the most recent date.
I made a sample set (see below). I want to know how many times '-1' shows up in the latest month (March). So the answer should be 3.
Date | Aspect | Value |
January 2020 | Responsibility | 1 |
January 2020 | Validity | 0 |
January 2020 | Satefy | -1 |
January 2020 | Guarantee | -1 |
February 2020 | Responsibility | 0 |
February 2020 | Validity | -1 |
February 2020 | Satefy | 0 |
February 2020 | Guarantee | 0 |
March 2020 | Responsibility | -1 |
March 2020 | Validity | -1 |
March 2020 | Satefy | -1 |
March 2020 | Guarantee | 1 |
Solved! Go to Solution.
Hi, @Anonymous
Try adding format function to “Table”[Date]:
measure =
var _sel = -1
return
CALCULATE(COUNT('Table'[Value]),FORMAT('Table'[Date],"MMMM YYYY")=FORMAT(TODAY(),"MMMM YYYY"),'Table'[Value]=_sel)
Best Regards,
Community Support Team _ Eason
measure =
var _sel = -1
return
calculate(count(table[Value]),Table[date] = format(today(),"MMMM YYYY"),table[value] =_sel)
Hi @amitchandak ,
Thanks for your reply.
I cannot see if the measure works, because Power BI shows me the following:
Cannot load the data for this visual
In DAX-comparisons it is not supported to make comparisons of values of type Date with values of type Text.
I don't understand this, since the column Value is the type 'Whole value' and not 'Text'.
Hi, @Anonymous
Try adding format function to “Table”[Date]:
measure =
var _sel = -1
return
CALCULATE(COUNT('Table'[Value]),FORMAT('Table'[Date],"MMMM YYYY")=FORMAT(TODAY(),"MMMM YYYY"),'Table'[Value]=_sel)
Best Regards,
Community Support Team _ Eason
User | Count |
---|---|
102 | |
91 | |
84 | |
77 | |
71 |
User | Count |
---|---|
113 | |
104 | |
100 | |
73 | |
65 |