Forum Discussion
Lastnonblankvalue help
I have 2 tables, one with the date of the last change in wage of each ID and another with hours by each ID.
(dd-mm-yy)
| ID | Wage | Date Changed |
| 1 | 10 | 01-01-23 |
| 1 | 15 | 01-10-23 |
| 2 | 20 | 01-02-23 |
| 3 | 15 | 01-04-23 |
| 4 | 20 | 01-05-23 |
| 4 | 25 | 01-08-23 |
| 5 | 10 | 01-06-23 |
| 5 | 15 | 01-10-23 |
Dimension table.
(dd-mm-yy)
| ID | Hours | Date |
| 5 | 166 | 11-09-23 |
| 5 | 171 | 16-11-23 |
| 5 | 165 | 27-11-23 |
| 3 | 178 | 08-08-23 |
| 4 | 179 | 18-03-23 |
| 3 | 162 | 02-03-23 |
| 2 | 165 | 12-02-23 |
| 4 | 166 | 28-02-23 |
| 1 | 179 | 11-10-23 |
| 1 | 165 | 14-07-23 |
| 4 | 169 | 04-03-23 |
| 2 | 174 | 12-05-23 |
| 2 | 167 | 11-02-23 |
| 1 | 165 | 31-10-23 |
| 5 | 172 | 06-12-23 |
| 1 | 166 | 05-07-23 |
| 1 | 167 | 18-01-23 |
| 3 | 165 | 23-08-23 |
| 4 | 169 | 17-06-23 |
| 3 | 167 | 25-03-23 |
| 4 | 178 | 21-09-23 |
| 5 | 172 | 31-10-23 |
| 5 | 170 | 26-12-23 |
| 3 | 169 | 10-06-23 |
| 1 | 162 | 01-02-23 |
| 4 | 165 | 22-11-23 |
Fact table.
I wanna get the wage * hours for each ID. But each wage has to be the last one available before or equal the date of the wage (Date Changed).
If I take the ID 4 for example, on June, it would be 169*20 = 3380.
If I take the ID 4, on November, it would be 165*25 = 4125.
If I select ID, November and June, it would be 4125+3380 = 7505.
And so on.
I can get the values correctly if I select one ID, but if I need to see every ID together, it seems not to work.
The measure I'm using looks something like this:
Wage Value =
VAR _data = MAX(dCalendario[Data])
VAR _dataEfetiva =
CALCULATE(
AVERAGEX(Table1, MAX(Table1[Date Change])),
dCalendario[Data],
FILTER(
Table1,
LASTNONBLANKVALUE(Table1[Date Change], Table1[Date Change] <= _data)
)
)
RETURN
/*CALCULATE(
MAX(PSO_TAXA_HISTORICO[DT_EFETIVA]),
ALL(PSO_TAXA_HISTORICO[DT_EFETIVA])
)*/
CALCULATE(
SELECTEDVALUE(Table1[Wage]) * SUM(Table2[Hours]),
Table1[Date Change] = _dataEfetiva
)
Thanks
1 Reply
Since these events are immutable you don't need to use measures. Calculated columns are sufficient.
You have problems in you Wage Changes table - there are entries in the fact table that precede the first wage change.
This is impacting IDs 3 and 4. Please correct the Changes table. Pegging the missing values at 10 would give:
