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 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?

 

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)
    )



4 Replies

  • LasseLJørgensen's avatar
    LasseLJørgensen
    Regular Visitor
    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)
    )



  • Dangar332's avatar
    Dangar332
    Resident Rockstar
    2 years ago

    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 )
        )
  • 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.