topic Get Last Year Amount from YYYYMM in DAX Commands and Tips

Get Last Year Amount from YYYYMM

Yukiya — Wed, 05 Oct 2022 01:41:02 GMT

Hi there,

I tried to set up DAX in Power Pivot to obtain last year from YYYYMM as list below.

Current DAX is "Amount (LY) = CALCULATE(SUM(GL[Amount]), 'GL'[YYYYMM]-100)", but the DAX does not work propery.

Could you kindly advise to me where I should correct the DAX?

YYYYMM	Amount	Amount (LY)
201801	- 139,101,683.92
201802	25,810,660.25
201803	28,855,821.88
201804	23,753,866.33
201805	23,132,956.66
201806	26,315,256.09
201807	24,520,892.76
201808	23,623,829.00
201809	26,456,274.47
201810	21,955,153.72
201811	22,498,678.50
201812	26,384,575.55
201901	- 189,770,598.72	- 139,101,683.92
201902	15,615,869.83	25,810,660.25
201903	16,314,134.57	28,855,821.88
201904	11,618,911.79	23,753,866.33
201905	20,767,728.55	23,132,956.66
201906	17,411,139.58	26,315,256.09
201907	16,906,384.34	24,520,892.76
201908	15,872,538.74	23,623,829.00
201909	63,597,458.01	26,456,274.47
201910	17,942,715.81	21,955,153.72
201911	20,393,510.18	22,498,678.50
201912	18,930,709.89	26,384,575.55
202001	- 143,565,098.61	- 189,770,598.72
202002	10,269,560.19	15,615,869.83
202003	10,668,221.20	16,314,134.57
202004	15,164,857.73	11,618,911.79
202005	15,332,134.94	20,767,728.55
202006	14,886,636.87	17,411,139.58
202007	19,078,204.64	16,906,384.34
202008	16,101,640.24	15,872,538.74
202009	18,056,134.10	63,597,458.01
202010	22,130,223.09	17,942,715.81
202011	18,344,649.74	20,393,510.18
202012	53,349,184.98	18,930,709.89

Regards,

Yukiya

Re: Get Last Year Amount from YYYYMM

amitchandak — Wed, 05 Oct 2022 01:50:23 GMT

Yukiya , if you have date or create date usingYYYYMM

date = datevalues([YYYYMM]& "01")

Join with date table and have YYYYMM column there too

YYYYMM = format([Date], "YYYYMM")

then use measure like

Year behind Sales = CALCULATE(SUM(Table[Amount]),dateadd('Date'[Date],-1,Year))
Year behind Sales = CALCULATE(SUM(Table[Amount]),SAMEPERIODLASTYEAR('Date'[Date]))

Re: Get Last Year Amount from YYYYMM

Yukiya — Wed, 05 Oct 2022 04:18:54 GMT

Hi amitchandak,

Thank you for your help.

I can implement new measure, but LY field is dosppeared once Financial Year.

I'll investigate how fix this issue.

Thanks,

Yukiya Ishimaru

Re: Get Last Year Amount from YYYYMM

v-yadongf-msft — Wed, 02 Nov 2022 09:12:43 GMT

Hi Yukiya ,

Has your problem been solved? If not solved, can you tell me what's your expected output?

Best regards,

Yadong Fang

Re: Get Last Year Amount from YYYYMM

Yukiya — Fri, 28 Oct 2022 00:40:26 GMT

Hi Yadong,

Thank you for your help.

This issue is not fixed yet, but I'll think another solution because we use Fiscal Year not Caledar Year.

Regards,

Yukiya Ishimaru

Re: Get Last Year Amount from YYYYMM

v-yadongf-msft — Fri, 04 Nov 2022 08:26:28 GMT

Hi Yukiya ,

Please try following DAX to create a new column:

Column = CALCULATE(SUM('Table'[Amount]),FILTER('Table', MONTH('Table'[YYYYMM]) = MONTH(EARLIER('Table'[YYYYMM])) && YEAR('Table'[YYYYMM]) = YEAR(EARLIER('Table'[YYYYMM]))-1))

You will get the result you want:

Best regards,

Yadong Fang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Get Last Year Amount from YYYYMM

Yukiya — Tue, 08 Nov 2022 23:12:29 GMT

Hi Yadong,

Thank you for your suggestion.

I try it later.

Regards,

Re: Get Last Year Amount from YYYYMM

Margot123 — Thu, 16 Feb 2023 16:02:44 GMT

Hello Yadong , I have a smilar issue when calculating previous month from YYYYMM

Would you know what DAX to create in the column?

Thank you very much,

Kind regards,

Maggie