Forum Discussion

LasseLJørgensen's avatar
LasseLJørgensen
Regular Visitor
2 years ago
Solved

Running total using datediff in a measure

I need a dynamic measure that can make a running total  summing the amount of days between two dates per employee. I want the measure to be able to run in 12 months 'windows'. Please regard I am not ...
DATEDIFF
Running Totals
  • LasseLJørgensen's avatar
    LasseLJørgensen
    2 years ago

    Anonymous 

    Hi, thx for the advice, but I couldn't get your idea to work. 

    I've worked on it some more and got it to work with this:

    Measure =

    VAR max_dato = MAX('Data'[Date paid])
    VAR min_dato = FIRSTDATE(SAMEPERIODLASTYEAR('Data'[Date paid]))
    RETURN
    CALCULATE(SUM('Data'[DIFF]),

    FILTER(ALL('Data'),
            'Data'[Date paid] <= max_dato
            && 'Data'[Date incurred] >= min_dato)
    )



Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi LasseLJørgensen ,

 

According to your description, here are my steps you can follow as a solution.

(1) My test data is the same as yours.

(2) We can create measures. 

Measure = DATEDIFF(MAX('Table'[Date incurred]),MAX('Table'[Date paid]),DAY)
Measure 2 = 
var _a=SUMX(
    SUMMARIZE(
      FILTER(ALL( 'Table'),'Table'[Date paid]<=MAX('Table'[Date paid])),
        'Table'[Employee number],
        'Table'[Date incurred],'Table'[Date paid],
        "total",[Measure]
    ),
    [total]
)
var _b=IF([Measure]=0,0,_a)
return CALCULATE(_b,DATESINPERIOD('Table'[Date paid], MAX('Table'[Date paid]), -1, YEAR))

(3) Then the result is as follows.

 

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

 

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.