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.
Hi, I tried to create DAX measure that will flag every end of latest available date each month to one. Later on, I am going to use this measure as visual level filter. The illustration is given in the table below.
For instance:
a. In January 2018, the latest available date is 10 Jan 2018, so that row will be flagged to 1
b. In February 2018, the latest available date 28 February 2018, so that row will be flagged to 1
c. in March 2018, the latest available date is 2 march 2018, so that row will be flagged to 1.
d. the rest of the rows will be flagged to 0
I was thinking to use Calculate function combined with ENDOFMONTH Function like
FlagEOFMONTH = CALCULATE(1,FILTER(MyTable,ENDOFMONTH(MyTable[DateNational]))), but it does not works
Any idea about how to generate correct dax measure ?
Date | Value | Flag |
1-Jan-18 | 20 | 0 |
2-Jan-18 | 20 | 0 |
3-Jan-18 | 20 | 0 |
4-Jan-18 | 12 | 0 |
5-Jan-18 | 17 | 0 |
6-Jan-18 | 12 | 0 |
7-Jan-18 | 15 | 0 |
8-Jan-18 | 11 | 0 |
9-Jan-18 | 20 | 0 |
10-Jan-18 | 18 | 1 |
1-Feb-18 | 15 | 0 |
2-Feb-18 | 20 | 0 |
3-Feb-18 | 15 | 0 |
4-Feb-18 | 11 | 0 |
5-Feb-18 | 14 | 0 |
6-Feb-18 | 14 | 0 |
7-Feb-18 | 17 | 0 |
8-Feb-18 | 14 | 0 |
9-Feb-18 | 19 | 0 |
10-Feb-18 | 18 | 0 |
11-Feb-18 | 16 | 0 |
12-Feb-18 | 14 | 0 |
13-Feb-18 | 10 | 0 |
14-Feb-18 | 11 | 0 |
15-Feb-18 | 17 | 0 |
16-Feb-18 | 19 | 0 |
17-Feb-18 | 10 | 0 |
18-Feb-18 | 12 | 0 |
19-Feb-18 | 18 | 0 |
20-Feb-18 | 16 | 0 |
21-Feb-18 | 15 | 0 |
22-Feb-18 | 18 | 0 |
23-Feb-18 | 15 | 0 |
24-Feb-18 | 10 | 0 |
25-Feb-18 | 15 | 0 |
26-Feb-18 | 16 | 0 |
27-Feb-18 | 14 | 0 |
28-Feb-18 | 19 | 1 |
1-Mar-18 | 17 | 0 |
2-Mar-18 | 18 | 1 |
Thanks
Andre
Solved! Go to Solution.
Hi @andrehawari
ENDOFMONTH function wont work as your latest date for the month could be any date, not necessarily to be month end.
Use the below formula t create a calculated column ( Not measure):
Monthend = IF(CALCULATE(MAX(Table1[Date]),FILTER(Table1,MONTH(Table1[Date])=MONTH(EARLIER(Table1[Date]))))=Table1[Date],1,0)
Thanks
Raj
Hi @andrehawari
ENDOFMONTH function wont work as your latest date for the month could be any date, not necessarily to be month end.
Use the below formula t create a calculated column ( Not measure):
Monthend = IF(CALCULATE(MAX(Table1[Date]),FILTER(Table1,MONTH(Table1[Date])=MONTH(EARLIER(Table1[Date]))))=Table1[Date],1,0)
Thanks
Raj
Perfect ! Thanks a lot Raj
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
99 | |
73 | |
72 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |