Get Last Year Amount from YYYYMM

[Yukiya]

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

[v-yadongf-msft · Accepted solution]

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.

[Margot123]

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

[Yukiya]

Hi Yadong,

Thank you for your suggestion.

I try it later.

Regards,

[v-yadongf-msft]

Hi @Yukiya ,

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

Best regards,

Yadong Fang

[Yukiya]

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

[amitchandak]

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

[Yukiya]

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