Accumulated revenue same period last year no slicer

[ITstod-IE]

I want to create a measure to be used in a KPI to work as our goal revenue since we don't have a budget to work with.

The wants for this report is to not have a filter on month/year, so the latest numbers will be last months closed accounting month.

I want a DAX measure that will calculate 202209 - 2022 last month.
To compare to 202309 - 2023 last month

I.e if we're in november I want to compare 202209-202210 to 202309-202310

This is the code I have for a total fiscal year 2023.
Any ideas how to edit it to fit my needs?

Accumulated lst yr =

CALCULATE (

    SUM('Revenue'[Earnings]),

    FILTER (

        ALL ('Fiscal year'),

        'Fiscal Year'[Year] = MAX ('Fiscal Year'[Year])-1

            && 'Fiscal Year'[MonthIndex] <= MAX ('Fiscal Year'[MonthIndex])

    )

)

[ITstod-IE · Accepted solution]

Thank you for taking your time to help me.
The DAX code didn't work for me, it returned the total fiscal year and not the equivalent months to be able to compare them.

I solved this with help from a collegue by creating a new column in Power query where we declare this month.
Then in DAX we wrote the below query which works for our needs.

Accumulated lst yr =

 var thismonth = MAX('Fiscal Year'[Todays month])

 return

CALCULATE (

    SUM('Revenue'[Earnings]),

    FILTER (

        ALL ('Fiscal Year'),

        'Fiscal Year'[Year] = MAX ('Fiscal Year'[Year])-1

            && 'Fiscal Year'[MonthIndex] <=  (thismonth-1)

))

[FreemanZ]

hi @ITstod-IE 

could you further explain this part:

I want a DAX measure that will calculate 202209 - 2022 last month.
To compare to 202309 - 2023 last month
I.e if we're in november I want to compare 202209-202210 to 202309-202310

For example,  is "-" an minus sign or range indicator?

[ITstod-IE]

Hi, sorry it's the range from september to october 2022 compared to september to october 2023.

Additionally if it's may then the last closed accounting month is april and I want to compare the range from september through april for 2022 and 2023.

[FreemanZ]

Hi @ITstod-IE 

on which date does your fiscal year start?

[ITstod-IE]

September 1st

[FreemanZ]

hi @ITstod-IE ,

not sure if i fully get you, supposing your Revenue table looks like:

date	earnings
9/1/2021	1
10/1/2021	1
11/1/2021	1
12/1/2021	1
1/1/2022	1
2/1/2022	1
3/1/2022	2
4/1/2022	2
5/1/2022	2
6/1/2022	2
7/1/2022	2
8/1/2022	2
9/1/2022	3
10/1/2022	3
11/1/2022	3
12/1/2022	3
1/1/2023	3
2/1/2023	3
3/1/2023	4
4/1/2023	4
5/1/2023	4
6/1/2023	4
7/1/2023	4
8/1/2023	4

try like:

1) create a dates table like:

dates = 
ADDCOLUMNS(
    CALENDAR(MIN(Revenue[Date]), MAX(Revenue[Date])),
    "YY/MM", 
    FORMAT([Date], "YY/MM")
)

2) write two measures like:

EarningsTtl = SUM(Revenue[Earnings])

YoY% =
VAR ThisYTD =
CALCULATE (
    [EarningsTtl],
    DATESYTD (dates[Date], "08-31" )
)
VAR LastYTD =
CALCULATE (
    [EarningsTtl],
    DATESYTD ( SAMEPERIODLASTYEAR ( dates[Date] ), "08-31" )
)
VAR _result = 
IF(
    ISBLANK(ThisYTD),
    BLANK()
    DIVIDE(ThisYTD, LastYTD) - 1
)
RETURN _result

3) plot a table visual with the dates[YY/MM] column and the YoY% measure. 

it worked like:

[ITstod-IE]

That is close to what I'm looking at but I need it in numbers

I wrote the measure acc LastYTD:

Ackumulerat period fg år = CALCULATE([EarningsTTL], DATESYTD(SAMEPERIODLASTYEAR('Date'[Date]), "08-31"))

The result below is what I get when I use the measure to compare this YTD to last YTD

[FreemanZ]

hi @ITstod-IE ,

then try like:

YoY =

VAR ThisYTD =

CALCULATE (

    [EarningsTtl],

    DATESYTD (dates[Date], "08-31" )

)

VAR LastYTD =

CALCULATE (

    [EarningsTtl],

    DATESYTD ( SAMEPERIODLASTYEAR ( dates[Date] ), "08-31" )

)

VAR _result = 

IF(

    ISBLANK(ThisYTD),

    BLANK()

    ThisYTD - LastYTD) 

)

RETURN _result