Running total using datediff in a measure

[LasseLJørgensen]

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 able to use window functions since i use analysis services which doesn't include window functions.

This is the data:

Among other attempts I have tried:

Running total =

CALCULATE(

    sumx(

       SUMMARIZE('Data', "result",

      DATEDIFF(MAX('Data'[Date incurred]), MIN('Data'[Date paid]), DAY ))

    , [result])

    , DATESINPERIOD('Data'[Date paid], MAX('Data'[Date paid]), -1, YEAR))

Anybody know of a solution where one can make running totals like that without using window functions?

[LasseLJørgensen · Accepted solution]

@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]

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. 

[LasseLJørgensen]

Hi @Dangar332 

That gives the same result. What I'm expectning is:
26-01-2014 = 5
14-02-2014 = 15
16-04-2014 = 33

and so on. 

[Dangar332]

hi,  @LasseLJørgensen 

try below

Running total =
var a = edate(min('Data'[Date paid]),-12)
var b = min('Data'[employee number])
return
var c  =sumx(
          filter( 
             'Data', 
             'Data'[employee number] = b &&
             'Data'[Date paid]<= min('Data'[Date paid]) && 'Data'[Date paid]>=a
          ), 
          DATEDIFF(MIN('Data'[Date incurred]), MIN('Data'[Date paid]), DAY )
        )