Display calculated measure for several months

[Seier-Alsenz]

Good day community,

I need your swarm knowledge with a probably simple problem.

I have a measurement and would like to display it as a time series, value per month. Thereby I have a filtering on only ONE monthYear.

e.g.
=Sum({$<MonthYear={$(vMonat-0)} >}Hours)+Sum({$<MonthYear={$(vMonat-1)} >}Hours)+Sum({$<MonthYear={$(vMonat-2)} >}Hours)...

This would show me in a chart the values according to the MonthYear. This is how I have used it so far. This also works so far.

I use this with variable YearMonth as following (the variable vMonat work fine):

= Sum({$<MonthYear={$(vMonat-1)} >}Hours)
/Sum({$<MonthYear={$(vMonat-1)}>}[Lenght])
/only({$<MonthYear={$(vMonat-1)}>}[Days])

Unfortunately, setting the expression in clamps for each month and adding "+" does not work.  Like this

= (Sum({$<MonthYear={$(vMonat-1)} >}Hours)
/Sum({$<MonthYear={$(vMonat-1)}>}[Lenght])
/only({$<MonthYear={$(vMonat-1)}>}[Days]))+(Sum({$<MonthYear={$(vMonat-0)} >}Hours)
/Sum({$<MonthYear={$(vMonat-0)}>}[Lenght])
/only({$<MonthYear={$(vMonat-0)}>}[Days]))

A single month works.
Adding multiple monaters does not work. The result is zeros.


The following formula works perfectly, with manual selection of each month.

Sum(Hours)
/Sum([Lenght])
/only([Days])



What is the problem here?

The fact that with manual month selection all calculations work cleanly, I am clueless and I do not know where the error should come from. So I have no approach to find a zero value, by wrong selection or similar (standard error).

[Chirantha]

Here are few things they could try to troubleshoot:
 

1)Test each part of the expression for each month individually to make sure all parts of the expressions are returning the expected results. You can create a table with MonthYear as a dimension and each part of the expression as a separate column.
 

2)Verify whether the 'Length' or 'Days' fields have null or zero values for any MonthYear, which might be causing the whole expression to be zero. You can do this by creating a table with MonthYear as a dimension and 'Length' and 'Days' as separate measures.
 

3) Check the variable 'vMonat'. Is it correctly set to give the desired MonthYear?

[Seier-Alsenz · Accepted solution]

Hi,

thank you for your anweser.

I solved the problem as follow. I used alt() for each measure and set complete expression in an alt()-function.

like this for two months:
=
(
alt((alt(Sum({$<MonthYear={ "$(vMonat-1)"}>}Hours),0)
/alt(Sum({$<MonthYear={ "$(vMonat-1)"}>}Lenght),0)
/alt(only({$<MonthYear={ "$(vMonat-1)"}>}Days),0)),0)
)
+
(
alt((alt(Sum({$<MonthYear={ "$(vMonat-0)"}>}Hours),0)
/alt(Sum({$<MonthYear={ "$(vMonat-0)"}>}Lenght),0)
/alt(only({$<MonthYear={ "$(vMonat-0)"}>}Days),0)),0)
)