Forum Discussion

Callmeadel's avatar
Callmeadel
New Member
4 years ago
Solved

RunningTotal to stop at max Date

Good afternoon,

 

I was just wondering what can of dax formula can I use to show the max date of my line

the orange line is Actual transaction amount and blue line is the Estimated amount problem is I dont want the orange line to keep going the max date should be may. 

 

Thank you!

 

  • v-zhangti's avatar
    v-zhangti
    4 years ago

    Hi, Callmeadel 

     

    You can try the following methods.

    Measure:

    RunningTotal =
IF (
    MONTH ( SELECTEDVALUE ( 'Table'[Date] ) ) <= MONTH ( TODAY () ),
    CALCULATE (
        SUM ( 'Table'[Actual transaction] ),
        FILTER ( ALL ( 'Table' ), [Date] <= SELECTEDVALUE ( 'Table'[Date] ) )
    ),
    BLANK ()
)

    Is this the result you expect?

     

    Best Regards,

    Community Support Team _Charlotte

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

5 Replies

    • Callmeadel's avatar
      Callmeadel
      New Member
      4 years ago

      Thank you for the reply, I have created that measure it did say we can't use it on the report so how can it be used to fitler the graph? Do I drag it to filters because it won't let me add it to values.

      • Callmeadel's avatar
        Callmeadel
        New Member
        4 years ago

        They wanted me to use it in a column and then use a slicer that makes sense, but my table doesnt have any data after may so everything is returning True which still keeps the line 😞

         

  • v-zhangti's avatar
    v-zhangti
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi, Callmeadel 

     

    You can try the following methods.

    Measure:

    RunningTotal =
IF (
    MONTH ( SELECTEDVALUE ( 'Table'[Date] ) ) <= MONTH ( TODAY () ),
    CALCULATE (
        SUM ( 'Table'[Actual transaction] ),
        FILTER ( ALL ( 'Table' ), [Date] <= SELECTEDVALUE ( 'Table'[Date] ) )
    ),
    BLANK ()
)

    Is this the result you expect?

     

    Best Regards,

    Community Support Team _Charlotte

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

    • Callmeadel's avatar
      Callmeadel
      New Member
      4 years ago

      Thank you zhangti, I will test your method out

       

      This is a method I used and worked great

       
      ActivateDates =
      var maxActiveDates= MAX('TR-Combined'[Date])
      var minactivedates= MIN('TR-Combined'[Date])
      return
      SWITCH(
      true(),
      DateCombined[Date]>= minactivedates&&
      DateCombined[Date]<=maxActiveDates,1,0)
       
       
      Cumulative Transcaction =
      SWITCH(
      true(),
      Max(DateCombined[ActivateDates])=1
      CALCULATE(
      sum('TR-Combined'[Spent]),
      all(DateCombined[Date]),
      DateCombined[Date]<=MAX(DateCombined[Date])))