Get SUM of current Year Month and Last Year until same Month

[reporting_neu]

Hello,

I'm creating a bar chart. This should display the values ​​of the months from the current year with the values ​​of the same months in the past year. However, I can't manage that not all values ​​from the past year are displayed. I only need these up to the current month. So today only January to February.

My formula looks like this:

SUM({1<Sales.Year={$(=max(Sales.Year)-12),$(=max(Sales.Year))}, Group = {'ABC'}>} [Sales])

I also have the following times to choose from:

• Year
• YearMonth = 202401 (2024 January)
• MonthYear = 012024 (January 2024)

How can I limit the last year (the months) so that only the months up to today are shown?

[anat]

=SUM({<YearMonth = {"<=$(=Date(MonthStart(Today()),'YYYYMM')),'YYYYMM'))>=$(=Date(MonthStart(Today(),-12),'YYYYMM'))"}>}Amount)

[Chanty4u]

Try this

SUM({<Sales.Year = {"$(=Max(Sales.Year))"}, 

     Sales.MonthYear = {"<=$(=Max(Sales.MonthYear))"}, 

     Group = {'ABC'}>} [Sales])

[reporting_neu · Accepted solution]

Thank you for your support. Unfortunately, none of the formulas led me to the desired solution.

But I still found a solution. I merged the year and month in the script and generated a date from it:

MakeDate(Year, Month, 1) as Sales.Date

I was then able to combine the formulas for the current year and the last year so that I could display both values together in one diagram:

SUM({1<Sales.Date = {">=$(=YearStart(MAX(Sales.Date)))<=$(=MonthEnd(MAX(Sales.Date)))"}, Group= {'ABC'}>}Sales.Sales)

+

SUM({1<Sales.Date = {">=$(=YearStart(MAX(Sales.Date),-1))<=$(=MonthEnd(MAX(Sales.Date),-12))"}, Group= {'ABC'}>}Sales.Sales)

 The result now looks like this: