Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Dynamic Running Total Month

Hello!

 

I would appreciate if you could help me or give some any help.

 

Sales Amount RT Year =

VAR FirstVisibleDate = MIN ( 'Date'[Date] )

VAR LastVisibleDate = MAX ( 'Date'[Date] )

VAR LastDateWithSales = CALCULATE ( MAX ( 'Sales'[Order Date] ), REMOVEFILTERS () )

VAR Result =

IF (

FirstVisibleDate <= LastDateWithSales,

CALCULATE (

[Sales Amount],

'Date'[Date] <= LastVisibleDate,

VALUES ('Date'[Calendar Year] )))

RETURN

Result

 

But when i filter by month, the running total is not correct.

 

Thank you!

Need Help
  • ryan_mayu's avatar
    ryan_mayu
    5 years ago

    Anonymous 

    pls try this

    Measure = 
VAR _min=CALCULATE(min('date'[Date]),ALLSELECTED('date'))
VAR _date=max('date'[Date])
VAR _min2=date(year(_date),month(_min),1)
return CALCULATE(SUM('Table'[value]),FILTER(all('Table'),'Table'[date]>=date(year(_date),month(_min),1)&&'Table'[date]<=_date))

    pls see the attachment below

3 Replies

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    5 years ago

    Anonymous 

    do you want to keep the RT value?

    Measure = 
VAR _date=max('date'[Date])
return CALCULATE(SUM('Table'[value]),FILTER(all('Table'),'Table'[date]>=date(year(_date),1,1)&&'Table'[date]<=_date))

    pls see the attachment below

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi, ryan_mayu 

       

      Thanks for the help! it gives me another ideas to solve other future situations, but didn't help me in that one. 

       

      it has to be similar to that one ( i know its kinda tricky but i want to solve this scenario just for fun 😉 ) 

       

       

       

      • ryan_mayu's avatar
        ryan_mayu
        Super User
        5 years ago

        Anonymous 

        pls try this

        Measure = 
VAR _min=CALCULATE(min('date'[Date]),ALLSELECTED('date'))
VAR _date=max('date'[Date])
VAR _min2=date(year(_date),month(_min),1)
return CALCULATE(SUM('Table'[value]),FILTER(all('Table'),'Table'[date]>=date(year(_date),month(_min),1)&&'Table'[date]<=_date))

        pls see the attachment below