Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Cumulative data on a period

Hello,

 

I have a problem with Power BI, 

 

I use a table like this

 

1/01 1

1/01 1

2/01 1

5/01 -1

 

And I  want to make a cumulate value like this 

 

1/01 2

2/01 3

5/01 2

 

I use this DAX function

 

CALCULATE(
    SUM('transitions_state'[Submitted]),
    FILTER(
        ALLSELECTED('transitions_state'[Data.ChangedDate]),
        ISONORAFTER('transitions_state'[Data.ChangedDate], MAX('transitions_state'[Data.ChangedDate]), DESC)
    )
)
 
 
And it works with my data
 

But when I use the date slicer, for exemple focus on 3/03 and 16/03 , i have this

 

 

I want that when i use date slicer, the data focus on the date selected but not affect the values. How can i do this ? 

 

Thank you in advance 

Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Anonymous ,

    Please try below steps:

    1. below is my test table

    Table:

    Date:

    Date = CALENDAR(FIRSTDATE('Table'[Date]),LASTDATE('Table'[Date]))

    Model:

    2. create a measure with below dax formula

    Measure =
VAR cur_date =
    SELECTEDVALUE ( 'Table'[Date] )
VAR tmp =
    FILTER ( ALL ( 'Table' ), [Date] <= cur_date )
RETURN
    SUMX ( tmp, [Value] )

    3.  add a table visual with Table field and measure, add a slicer visual with Date table field

    Please refer the attached .pbix file.

     

    Best regards,
    Community Support Team_Binbin Yu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    2 years ago

    You do that by using a disconnected table to feed your slicer.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous ,

    Please try below steps:

    1. below is my test table

    Table:

    Date:

    Date = CALENDAR(FIRSTDATE('Table'[Date]),LASTDATE('Table'[Date]))

    Model:

    2. create a measure with below dax formula

    Measure =
VAR cur_date =
    SELECTEDVALUE ( 'Table'[Date] )
VAR tmp =
    FILTER ( ALL ( 'Table' ), [Date] <= cur_date )
RETURN
    SUMX ( tmp, [Value] )

    3.  add a table visual with Table field and measure, add a slicer visual with Date table field

    Please refer the attached .pbix file.

     

    Best regards,
    Community Support Team_Binbin Yu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.