DAX - LASTNONBLANK per dimension

Question

I have a table with salary payments across departments ("Days from Y" = days since start of year, "Days to M" = days to end of month)

Department | Salary | Date       | Type            | Days from Y | Days to M
-----------+--------+------------+-----------------+-------------+-----------
Finance    |     71 | 01-01-2016 | Regular payment |           1 |        30
Sales      |   3000 | 20-01-2016 | Regular payment |          20 |        11
Sales      |   -300 | 21-01-2016 | Correction      |          21 |        10
Finance    |   2000 | 01-02-2016 | Regular payment |          32 |        27
Sales      |   3100 | 15-02-2016 | Regular payment |          46 |        12

For regular payments, the salary needs to be corrected to present as if it was a full month. But, in the next month the correction of the previous month must not be included (because it's already provided for in the new salary) - only the correction of the last month should be included!

For Sales, that would be:

Date           | Salary | Salary (cum.) | Correction | Salary (corr.) cum.
---------------------------------------------------------------------------
2016           |   5800 |          5800 |            |            
  2016-01      |   2700 |          2700 |       1650 |                4350
    2016-01-20 |   3000 |          3000 |       1650 |                4650
    2016-01-21 |   -300 |          2700 |            |                4350
  2016-02      |   2550 |          5250 |       2040 |                7290
    2016-02-15 |   2550 |          5250 |       2040 |                7290

Calculating the correction itself is quite easy: if the it's a regular payment, then use that date to calculate the correction for the given month-department combination.

Using a LASTNONBLANK expression, I can make a correct cumulative measure that works for a single department:

Salary (corr.) cum := CACLULATE(MAX([Correction]); LASTNONBLANK([Date]; MAX([Correction])

However, this doesn't work across departments - for 2016-01 that would lead to wrong total counters:

Department | Salary | Salary (cum.) | Correction | measure | should be
-----------------------------------------------------------------------
(Total)    |   2771 |          3071 |            |    4721 |      6851    
  Finance  |     71 |            71 |       2130 |    2201 |      2201
  Sales    |   2700 |          3000 |       1650 |    4650 |      4650

How do I create a measure that correctly calculates the corrections for each month, as well as gets the totals correct?

_{(so basically it looks to the last correction for each department (or other dimension) and uses the sum of these instead of the last correction across all dimensions)}

Alberto Ferrari Alberto Ferrari · Accepted Answer · 2016-09-02T06:37:44

You basically need to iterate over the departments.

Salary (corr.) cum := 
SUMX (
    Departments,
    CACLULATE(MAX([Correction]); LASTNONBLANK([Date]; MAX([Correction])
)

That should do the trick.

Alberto

DAX - LASTNONBLANK per dimension

1 Answers