Need to show running total of all months

[Sebastiaandb]

Hi guys,

My goal is that i want to see the running total values for every month in the table (see picture).

Therefore i need my variable to ignore the filter for 'maand'. 

However, it doesn't seem to work as it only shows the running total for the specific month.

Could someone help me out?

if(Date([JAAR_PERIODE_DATE.autoCalendar.YearMonth])< (date([Rapportagemaand.autoCalendar.YearMonth])),
RangeSum(above(total sum({<
INTERN_LAAG_1_OMSCHRIJVING = {"Operationele kosten"},
Maand =
>}
REALISATIE)
, 0, RowNo(total))))

[sunny_talwar]

May be give this a shot

If(Only({<Maand>}[JAAR_PERIODE_DATE.autoCalendar.YearMonth]) < Only({<Maand>}[Rapportagemaand.autoCalendar.YearMonth]),
RangeSum(Above(TOTAL Sum({<INTERN_LAAG_1_OMSCHRIJVING = {"Operationele kosten"}, Maand>} REALISATIE), 0, RowNo(TOTAL)))
)

or this

If(Only({<Maand>}[JAAR_PERIODE_DATE.autoCalendar.YearMonth]) < Only([Rapportagemaand.autoCalendar.YearMonth]),
RangeSum(Above(TOTAL Sum({<INTERN_LAAG_1_OMSCHRIJVING = {"Operationele kosten"}, Maand>} REALISATIE), 0, RowNo(TOTAL)))
)

[Sebastiaandb]

He Sunny,

First of all i'm honored to receive a reply from you!

I have tried the solution out but it still only shows the running total for the selected months.  It seems the set analysis doesn't work in combination with the first line of code. As i exclude the first line, it does show me the running totals for each month. However then the restriction on < date is removed and it shows the running total for all the months in the year. Off course that's not what we want, we only want to show the values before a certain date. 

//if(only({<Maand>}[JAAR_PERIODE_DATE.autoCalendar.YearMonth])< only([Rapportagemaand.autoCalendar.YearMonth]),
RangeSum(above(total sum({<
INTERN_LAAG_1_OMSCHRIJVING = {"Operationele kosten"},
Maand =
>}
REALISATIE)
, 0, RowNo(total)))

[sunny_talwar]

How about this? Not sure what the first condition is for, but if the goal is to show all Months less than the selected months, may be try this

If(Only({<Maand>}Maand) < MaxString(TOTAL Maand),
RangeSum(Above(TOTAL Sum({<INTERN_LAAG_1_OMSCHRIJVING = {"Operationele kosten"}, Maand>} REALISATIE), 0, RowNo(TOTAL)))
)

[Sebastiaandb]

He Sunny!

Thanks again, almost there!

Now it shows all the running totals including the selected month (i added '=' to your code):

If(Only({<Maand>}Maand) <= MaxString(TOTAL Maand),
RangeSum(Above(TOTAL Sum({<INTERN_LAAG_1_OMSCHRIJVING = {"Operationele kosten"}, Maand>} REALISATIE), 0, RowNo(TOTAL)))
)

The only other thing that is important to us, is that we only want it to show data before a given date.

This date is represented in a table and reffered to as [Rapportagemaand.autoCalendar.YearMonth]).  

For example, the date in that table is now '4/1/2019' and therefore the dashboard should only show data for january, february and march. 

In my first code i accomplished this by: 

if(Date([JAAR_PERIODE_DATE.autoCalendar.YearMonth])< (date([Rapportagemaand.autoCalendar.YearMonth]))

But as you know that doesn't seem to work in combination with showing the running totals for all months including the selected month. 

[sunny_talwar]

May be try this

If(

Only({<Maand>}Maand) <= MaxString(TOTAL Maand)
and
Only({<Maand>} [JAAR_PERIODE_DATE.autoCalendar.YearMonth]) < Only({<Maand>}[Rapportagemaand.autoCalendar.YearMonth])
,
RangeSum(Above(TOTAL Sum({<INTERN_LAAG_1_OMSCHRIJVING = {"Operationele kosten"}, Maand>} REALISATIE), 0, RowNo(TOTAL)))
)

or even this

If(

Only({<Maand>}Maand) <= MaxString(TOTAL Maand)
and
Only({1} [JAAR_PERIODE_DATE.autoCalendar.YearMonth]) < Only({1}[Rapportagemaand.autoCalendar.YearMonth])
,
RangeSum(Above(TOTAL Sum({<INTERN_LAAG_1_OMSCHRIJVING = {"Operationele kosten"}, Maand>} REALISATIE), 0, RowNo(TOTAL)))
)

[Sebastiaandb]

He Sunny,

I've tried your solutions but after applying it returns to only showing the running total for the selected month. 

Thank you anyway for helping me! I really appreciate it.

[Sebastiaandb]

He Sunny,

i've got the script almost working. Instead of using the date variable [Rapportagemaand.autoCalendar.YearMonth] i've used a 'Periode' variable (what means period in Dutch) to make show the running total for all months smaller or equal to period 3. However, as i'm not using the yearmonth variable anymore this also means that for the years <2019 it also only shows the running total for the months jan, feb and march. In the code below i tried to add in an additional if statement to restrict this effect to only the year 2019 but that doesn't seem to work. If that would've worked i could've easilly copied the original code after the 'else' expression. I really start to wonder if i can solve this puzzle haha...

if(
//JAAR = MaxString(total JAAR),
//if(
only({<Maand>}Maand) <= max(total Periode)
and
Only({<Maand>}Maand) <= MaxString(TOTAL Maand)
,
RangeSum(Above(TOTAL Sum({<INTERN_LAAG_1_OMSCHRIJVING = {"Operationele kosten"}, Maand>} REALISATIE), 0, RowNo(TOTAL))))
//)

[Sebastiaandb]

He Sunny,

i've got the script almost working. Instead of using the date variable [Rapportagemaand.autoCalendar.YearMonth] i've used a 'Periode' variable (what means period in Dutch) to make show the running total for all months smaller or equal to period 3. However, as i'm not using the yearmonth variable anymore this also means that for the years <2019 it also only shows the running total for the months jan, feb and march. In the code below i tried to add in an additional if statement to restrict this effect to only the year 2019 but that doesn't seem to work. If that would've worked i could've easilly copied the original code after the 'else' expression. I really start to wonder if i can solve this puzzle haha...

if(       	
   //JAAR = MaxString(total JAAR),
   //if(
only({<Maand>}Maand) <=  max(total Periode)and
Only({<Maand>}Maand) <= MaxString(TOTAL Maand)
,
RangeSum(Above(TOTAL Sum({<
INTERN_LAAG_1_OMSCHRIJVING = {"Operationele kosten"}, 
Maand
>}
 REALISATIE), 0, RowNo(TOTAL))))
 //)

[Sebastiaandb · Accepted solution]

He Sunny,

I've figured out a solution, partly based on the solution you provided 🙂 (see code below). I replaced the date variable for a nummerical variable ('Jaarperiodenummeriek' and 'Periodejaar') that combines the year and the month, f.e. '2019-03'. 

I don't fully understand why it does the trick by replacing the date variable but it satisfies my need anyhow :-). Again, thanks for the help!

                  if( 		
			only({<Maand>}[Jaarperiodenummeriek]) <  only(total [Periodejaar])
			and
			Only({<Maand>}Maand) <= MaxString(TOTAL Maand)
      		//and
			//only({<Jaarperiodenummeriek>}Jaarperiodenummeriek) <  max(total Periodejaar)

 		,
			RangeSum(Above(TOTAL Sum(
            {<
            INTERN_LAAG_1_OMSCHRIJVING = {"Operationele kosten"},
            Jaarperiodenummeriek =,
            Maand =
            >} 
            REALISATIE)
            , 0, RowNo(TOTAL))))