Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Constrained Running Total

Hi all, my first post/question! 

 

New to Power BI, enjoying learning.

 

I have a single table data model that has columns like this:

 

Stuf...Type....Value...Data...Stuff

 

For each "Type", I have a serveral Value and Date pairs associated.

 

What I would like to be able to do is create a "CumulativeValue" measure so that I can chart this information in a Line Chart (where each "Type" is a Legend entry).

 

I have seen DAX like this:

 
CumulativeX = CALCULATE(Table[X],filter(all(Table[Date]), Table[Date] <= max(Table[Date])))
 
However, this is not precisely what I am after as the *date range* for each distinct Type of data is different.
 
What I would like to be able to do is accumulate a running total for a Value that honors the max Date for each Type.
 

I would really appreciate any suggestions or pointers on how to do this!

 

Thanks,

 

Doug

Need Help
  • Mariusz's avatar
    Mariusz
    6 years ago

    Hi Anonymous 

     

    Try something like this

    CumulativeX =
CALCULATE (
    [X],
    FILTER ( 
        ALLEXCEPT( Table, Table[Type] ), 
        Table[Date] <= MAX ( Table[Date] ) 
    )
)

     

    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.

    Please feel free to connect with me.
    LinkedIn

     

2 Replies

  • Mariusz's avatar
    Mariusz
    Community Champion
    6 years ago

    Hi Anonymous 

     

    Try something like this

    CumulativeX =
CALCULATE (
    [X],
    FILTER ( 
        ALLEXCEPT( Table, Table[Type] ), 
        Table[Date] <= MAX ( Table[Date] ) 
    )
)

     

    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.

    Please feel free to connect with me.
    LinkedIn

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Many thanks Mariusz!