Forum Discussion

Saap's avatar
Saap
Resolver III
4 years ago
Solved

Count running total between marked dates

Hi all, In my table I have following columns: [ContractStart], [ContractStop], [Client], [Employee] and, [ContractDuration]. I also have 3 measures: [ACTIVE] - marks the contract active in a give...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Saap ,

     

    You could create a group based on [OUTFLUX].

    M_group = CALCULATE(MIN('Table'[ContractStart]),FILTER(ALL('Table'),[M_OUTFLUX]=1&&'Table'[ContractStart]>=SELECTEDVALUE('Table'[ContractStart])))

    Then calculate the running total based on the group.

    Measure = CALCULATE(SUM('Table'[ContractDuration]),FILTER(ALLSELECTED('Table'),[M_group]=MAXX('Table',[M_group])&&'Table'[ContractStart]<=SELECTEDVALUE('Table'[ContractStart])))

    Pbix as attached.

     

    Best Regards,

    Jay

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi Saap ,

 

You could create a group based on [OUTFLUX].

M_group = CALCULATE(MIN('Table'[ContractStart]),FILTER(ALL('Table'),[M_OUTFLUX]=1&&'Table'[ContractStart]>=SELECTEDVALUE('Table'[ContractStart])))

Then calculate the running total based on the group.

Measure = CALCULATE(SUM('Table'[ContractDuration]),FILTER(ALLSELECTED('Table'),[M_group]=MAXX('Table',[M_group])&&'Table'[ContractStart]<=SELECTEDVALUE('Table'[ContractStart])))

Pbix as attached.

 

Best Regards,

Jay

Saap's avatar
Saap
Resolver III
4 years ago

Hi Anonymous 

Thank you for your solution.
It works perfectly in your pbix file but I have problems implementing it in my report.
It seems that these measures are very performance heavy and the visual couldn't load:

It looks like it can't handle bigger amounts of data (my fact table contains about 200k rows).

Fortunately I found other solution. I just had to put the column CONTRACTS[Date of influx fixed] (I counted it earlier, you can see the code in my previous post) into the matrix's rows and then my measure counted the rolling total correctly.

I am not sure why it works that way. Maybe you could explain it to me?

Anyway thank you. I will accept your post as a solution 🙂