I'm trying to create a measure to calculate the sum of days absence but grouped by unique absence ID.
I have the following formula, but I keep getting an error and I just can't figure out why :/
Measure 1:=
CALCULATE(
SUM('Master Absence Dates'[Approved]),
FILTER(
ALL('Master Absence Dates'),
'Master Absence Dates'[Unique ID]=EARLIER('Master Absence Dates'[Unique ID],1)
)
)
If anyone could shed some light on it and explain what I'm doing wrong that would be great - would love to learn how to use this formula properly! I'm wondering if i'm just attempting to use a measure like a calculated column and maybe that's just wrong in iteself!
Thank you :)
Edited to update question: So, I am looking to create the measure to sum only the values in the column 'Approved' once per unique value in column 'Unique ID Occurrence'. So that I get the total amount of workdays taken off - the 'Approved' column repeats the total value for the occurrence hence why i need to sum it once per unique ID :)
| Unique ID Occurrence | Approved |
|---|---|
| 120115-20/01/2023 | 2 |
| 121165-23/01/2023 | 4 |
| 121008-23/01/2023 | 4 |
| 121428-09/01/2023 | 50 |
| 120007-23/01/2023 | 30 |
| 121165-23/01/2023 | 4 |
| 121008-23/01/2023 | 4 |
| 121428-09/01/2023 | 50 |
| 120007-23/01/2023 | 30 |
| 148341-25/01/2023 | 3 |
| 121165-23/01/2023 | 4 |
| 120677-25/01/2023 | 1 |
| 149202-25/01/2023 | 1 |
| 121008-23/01/2023 | 4 |
| 121428-09/01/2023 | 50 |
| 120007-23/01/2023 | 30 |
| 148341-25/01/2023 | 3 |
| 121165-23/01/2023 | 4 |
| 120468-26/01/2023 | 1 |
| 150921-26/01/2023 | 2 |
| 120627-26/01/2023 | 1 |
| 121428-09/01/2023 | 50 |
| 120007-23/01/2023 | 30 |
| 148341-25/01/2023 | 3 |
You are correct, EARLIER needs row context, and measures (usually) don't have it. When it comes to measures, you don't need EARLIER but you will need to know what you want to filter on.
Try:
Measure 1:=
SUMX(
DISTINCT('Master Absence Dates'[Unique ID Occurrence]),
CALCULATE( MAX('Master Absence Dates'[Approved]) )
)