topic Re: Accumulated revenue same period last year no slicer in DAX Commands and Tips

Accumulated revenue same period last year no slicer

ITstod-IE — Mon, 23 Oct 2023 15:03:59 GMT

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])

)

Re: Accumulated revenue same period last year no slicer

FreemanZ — Tue, 24 Oct 2023 02:48:15 GMT

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?

Re: Accumulated revenue same period last year no slicer

ITstod-IE — Tue, 24 Oct 2023 06:00:58 GMT

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.

Re: Accumulated revenue same period last year no slicer

FreemanZ — Tue, 24 Oct 2023 06:46:42 GMT

Hi ITstod-IE

on which date does your fiscal year start?

Re: Accumulated revenue same period last year no slicer

ITstod-IE — Tue, 24 Oct 2023 06:55:23 GMT

September 1st

Re: Accumulated revenue same period last year no slicer

FreemanZ — Tue, 24 Oct 2023 07:37:31 GMT

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:

Re: Accumulated revenue same period last year no slicer

ITstod-IE — Tue, 24 Oct 2023 08:34:03 GMT

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

Re: Accumulated revenue same period last year no slicer

FreemanZ — Tue, 24 Oct 2023 09:10:14 GMT

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

Re: Accumulated revenue same period last year no slicer

ITstod-IE — Tue, 24 Oct 2023 11:42:24 GMT

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)

))