Fiscal Year Comparison

damen
damen Contributor
in Magic ETL

Hi,

I am trying to create a running total of fiscal year 2023 vs the same time in FY 2022 and FY 2021 but all on the same chart.

Right now, I currently have this

image.png
image.png


Essentially what I am trying to do is take the three green bars from July 1 of this year, combine them into one column and then do the same for the pink and blue. We have previously had someone here do all three columns with mySQL and when they have done that, the numbers don't turn out correctly.

Is there a way to group July, August and September of 2020, 2021, and 2022 together on one bar graph? The running total portion of that would be great so that we didnt have to figure out comparable week end for 2020 and 2021 each week as well.

TIA

If this helps, feel free to agree, accept or awesome it!

Answers

  • trafalger
    trafalger Coach

    I would create a month beast mode (Monthname(DateColumn)), drag that to your X-Axis and then make a year (Year(DateColumn)) beast mode and use that as the series.

  • damen
    damen Contributor

    @trafalger I'm confused by that answer. Maybe I can show something that would explain what I was aiming for a bit better. My apologies.

    image.png

    In the blue column is ALL of FY 2021 stats, Red 2022, Green 2023.

    The problem I am having is trying to filter YTD from fiscal year 2021 and 2022.

    Essentially I need it so the blue only shows 07/01/2020-10/03/2020 and the same thing in red except 2021 (again right now we have 07/01/2020-06/30/2021 in blue and 07/01/2021 - 6/30/2022 in red - which is an improper comparison for green)

    The green is correct in that it shows FY (07/01/2022 - Today)

    We are hoping to make a comparison on the first X (in this case, almost 3 months) amount of days in the FY

    If this helps, feel free to agree, accept or awesome it!

Categories