Forum Discussion

isThatAFrog's avatar
isThatAFrog
Frequent Visitor
3 years ago
Solved

cumulative Graph with dynamic start value

Hi Guys,

I have a graph that i cant get to filter on the effective date on because the query is created so that it calculates the startdata backwards and adds it the day before the first date, is there a way to keep the values from the first date when applying filter on the effective date
Formula

MoveCalc Cumulative =
CALCULATE([MoveCalc Total],
FILTER(ALLSELECTED('Mobility All'),
    'Mobility All'[Effective date] <= MAX('Mobility All'[Effective date])))
Without filter

With filter applied 

Maybe with some first date or ALL min value??

 

Many thanks for any help!

 

  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi  isThatAFrog , 

    I created some data: 

    Do you mean that in the filtered visual, the value of the current first date is the value of the first date after the filter plus the value of the day before the first date after the filter? 

     

    1. Create measure: 

    Measure = 
var _date= 
MINX(ALLSELECTED('Mobility All'),[Effective date]) 
var _mindate= 
MAXX( 
    FILTER(ALL('Mobility All'),'Mobility All'[Effective date]<_date),[Effective date]) 
return 
SUMX( 
    FILTER(ALLSELECTED('Mobility All'), 
   'Mobility All'[Effective date]<=MAX('Mobility All'[Effective date])),[MoveCalc Total]) 
+ 
SUMX( 
    FILTER(ALL('Mobility All'),'Mobility All'[Effective date]=_mindate),[MoveCalc Total])

     2. Result: 

    If it doesn't meet your expectations, can you express your results in the form of pictures, we can better help you 

     

    Best Regards, 

    Liu Yang 

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly 

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi  isThatAFrog , 

    I created some data: 

    Do you mean that in the filtered visual, the value of the current first date is the value of the first date after the filter plus the value of the day before the first date after the filter? 

     

    1. Create measure: 

    Measure = 
var _date= 
MINX(ALLSELECTED('Mobility All'),[Effective date]) 
var _mindate= 
MAXX( 
    FILTER(ALL('Mobility All'),'Mobility All'[Effective date]<_date),[Effective date]) 
return 
SUMX( 
    FILTER(ALLSELECTED('Mobility All'), 
   'Mobility All'[Effective date]<=MAX('Mobility All'[Effective date])),[MoveCalc Total]) 
+ 
SUMX( 
    FILTER(ALL('Mobility All'),'Mobility All'[Effective date]=_mindate),[MoveCalc Total])

     2. Result: 

    If it doesn't meet your expectations, can you express your results in the form of pictures, we can better help you 

     

    Best Regards, 

    Liu Yang 

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly