Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello all,
I have a table with projects on one column and Date Updated on another column as in the example below. I would like to add a new column "Most recent date" that will take value 1, if the day when the update took place is the highest in the month and 0 otherwise.
In the example below, 15 is the highest day in September and 23 is the highest day in October. That's way the value in the "Most recent date" is 1.
Any idea how to achieve this? Also, some intuition along the code will be very helpful. Thank you!
Project ID | Date Updated | Most recent date |
11 | 02/09/2020 | 0 |
12 | 02/09/2020 | 0 |
13 | 02/09/2020 | 0 |
14 | 02/09/2020 | 0 |
12 | 08/09/2020 | 0 |
14 | 08/09/2020 | 0 |
12 | 15/09/2020 | 1 |
14 | 15/09/2020 | 1 |
15 | 15/09/2020 | 1 |
16 | 15/09/2020 | 1 |
11 | 02/10/2020 | 0 |
12 | 02/10/2020 | 0 |
13 | 02/10/2020 | 0 |
14 | 02/10/2020 | 0 |
12 | 11/10/2020 | 0 |
14 | 11/10/2020 | 0 |
12 | 23/10/2020 | 1 |
14 | 23/10/2020 | 1 |
15 | 23/10/2020 | 1 |
16 | 23/10/2020 | 1 |
Solved! Go to Solution.
@Anonymous , a new column like
if( [Date Updated] = maxx(filter(table, format([Date Updated],"YYYYMM") = format(earlier([Date Updated]),"YYYYMM")),[Date Updated]) , 1,0)
Hi, @Anonymous
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
Table:
You may create a calculated column or a measure as below.
Calculated column:
Result Column =
var _maxdate =
CALCULATE(
MAX('Table'[DateUpdated]),
FILTER(
'Table',
YEAR([DateUpdated])=YEAR(EARLIER('Table'[DateUpdated]))&&
MONTH([DateUpdated])=MONTH(EARLIER('Table'[DateUpdated]))
)
)
return
IF(
[DateUpdated]=_maxdate,
1,0
)
Measure:
Result Measure =
var _maxdate =
CALCULATE(
MAX('Table'[DateUpdated]),
FILTER(
ALL('Table'),
YEAR([DateUpdated])=YEAR(SELECTEDVALUE('Table'[DateUpdated]))&&
MONTH([DateUpdated])=MONTH(SELECTEDVALUE('Table'[DateUpdated]))
)
)
return
IF(
SELECTEDVALUE('Table'[DateUpdated])=_maxdate,
1,0
)
Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @Anonymous
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
Table:
You may create a calculated column or a measure as below.
Calculated column:
Result Column =
var _maxdate =
CALCULATE(
MAX('Table'[DateUpdated]),
FILTER(
'Table',
YEAR([DateUpdated])=YEAR(EARLIER('Table'[DateUpdated]))&&
MONTH([DateUpdated])=MONTH(EARLIER('Table'[DateUpdated]))
)
)
return
IF(
[DateUpdated]=_maxdate,
1,0
)
Measure:
Result Measure =
var _maxdate =
CALCULATE(
MAX('Table'[DateUpdated]),
FILTER(
ALL('Table'),
YEAR([DateUpdated])=YEAR(SELECTEDVALUE('Table'[DateUpdated]))&&
MONTH([DateUpdated])=MONTH(SELECTEDVALUE('Table'[DateUpdated]))
)
)
return
IF(
SELECTEDVALUE('Table'[DateUpdated])=_maxdate,
1,0
)
Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Anonymous , a new column like
if( [Date Updated] = maxx(filter(table, format([Date Updated],"YYYYMM") = format(earlier([Date Updated]),"YYYYMM")),[Date Updated]) , 1,0)
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |