Compare time periods in barchart

[Kirsten]

I would like to compare time periods Jan 2019 – July 2019 with Jan 2020 – July 2020 in a barchart.  How to get that organised in the dimensions. See attachment for details 

[rubenmarin]

Hi, you can have a variable to store the max date to compare, like:

SET vMaxDate_CY = =Date(Floor(MonthEnd(AddMonths(Today(),-1))));

And another for last year:

SET vMaxDate_LY = =Date(AddYears(Floor(MonthEnd(AddMonths(Today(),-1))), -1));

And use this variables in set analysis:

Sum({<Date_Field={">=$(=YearStart('$(vMaxDate_CY)'))<=$(vMaxDate_CY)"}>} [Total Cost])
Sum({<Date_Field={">=$(=YearStart('$(vMaxDate_LY)'))<=$(vMaxDate_LY)"}>} [Total Cost])

[Kirsten]

Hi @rubenmarin ,

Thanks! A few additional questions:

• DoT I need to apply the first 2 codes in Master Items/Dimensions? And where to apply the last code.
• My variable name is YearMonth where to apply this in the code, is this everywhere where "Date" is added?
• I need to select a very specif time period (201901 until 201907, compared against 202001 until 202007) I do not see this selection in the code, where to add that, or how to create this specific selection?

[rubenmarin]

Hi @Kirsten 

• The first 2 codes are for variables (variables are a different entity than fields and master items), you can set that on script. just copy and paste in the first sheet/section of the script. The last codes are for expression, one is for curent year and another for last year.
• From each period you can create a Date field in script using: Date(Date#(YearMonth,'YYYYMM')) as Date
• In my example periods are based on the current date (it uses Today()).

If you want fixed periods for each expression it can be easier, you dot need anything of above, just use this expressions:

2020: Sum({<YearMonth={">=202001<=202007"}>} [Total Cost])
2019: Sum({<YearMonth={">=201901<=201907"}>} [Total Cost]))

[Kirsten]

Ah thanks @rubenmarin . these last 2 expressions  do what I need. Although it seems a bit impossible to have both one bar for jan-july 2020 and   one bar for jan-july 2019 in one graph. I can only add one measure

These are the expressions used 

Sum({<YearMonth={">=201901<=201907"}>} [Total Cost])

Sum({<YearMonth={">=202001<=202007"}>} [Total Cost])

See attachement 

[rubenmarin · Accepted solution]

That should be beacuse you alredy have 2 dimensions, ie: the year dimension.

You can remove this year dimension toa loow more expressions.

Or if you want dymanic years you can create a Month field and use:

Sum({<Month={"<=7"}>} [Total Cost])

You can create the month is script with: Right(YearMonth, 2)

[Kirsten]

Yes now it works as expected 🙂 Thanks!