Compare previous and new sales

[PKGARG]

I have Data Like,

Fin year	Bill Month	Qty
2015-2016	April	223
2015-2016	May	308
2015-2016	June	602
2015-2016	July	726
2015-2016	August	527
2015-2016	September	536
2015-2016	October	289
2015-2016	November	406
2015-2016	December	672
2015-2016	January	575
2015-2016	February	784
2015-2016	March	213
2016-2017	April	233
2016-2017	May	193
2016-2017	June	562
2016-2017	July	517
2016-2017	August	531
2016-2017	September	353
2016-2017	October	227
2016-2017	November	249
2016-2017	December	629
2016-2017	January	599
2016-2017	February	600
2016-2017	March	243

Now what i write in measure column to calculate monthwise growth.

[v-qiuyu-msft · Accepted solution]

Hi @PKGARG,

You can create calculated columns like below:

MonthNum = SWITCH('Table1'[Bill Month],"January",1,"February",2,"March",3,"April",4,"May",5,"June",6,"July",7,"August",8,"September",9,"October",10,"November",11,"December",12)

Year = IF('Table1'[MonthNum]>=4 && 'Table1'[MonthNum]<=12,VALUE(LEFT('Table1'[Fin year],4)),VALUE(LEFT('Table1'[Fin year],4)+1))

Date = DATE('Table1'[Year],'Table1'[MonthNum],1)

PreMon = CALCULATE(SUM(Table1[Qty]),PARALLELPERIOD('Table1'[Date],-1,MONTH))

Diff = 'Table1'[Qty]-Table1[PreMon]

Best Regards,
Qiuyun Yu

[v-qiuyu-msft · Accepted solution]

Hi @PKGARG,

You can create calculated columns like below:

MonthNum = SWITCH('Table1'[Bill Month],"January",1,"February",2,"March",3,"April",4,"May",5,"June",6,"July",7,"August",8,"September",9,"October",10,"November",11,"December",12)

Year = IF('Table1'[MonthNum]>=4 && 'Table1'[MonthNum]<=12,VALUE(LEFT('Table1'[Fin year],4)),VALUE(LEFT('Table1'[Fin year],4)+1))

Date = DATE('Table1'[Year],'Table1'[MonthNum],1)

PreMon = CALCULATE(SUM(Table1[Qty]),PARALLELPERIOD('Table1'[Date],-1,MONTH))

Diff = 'Table1'[Qty]-Table1[PreMon]

Best Regards,
Qiuyun Yu

[PKGARG]

Sorry, One Error has detected...

Show MSG..

A Circular dependency was detected: Table1[PreMon],Table1[Column],Table1[PreMon].

[v-qiuyu-msft]

@CahabaData Yes, you can download attached .pbix file and open it in desktop.

@PKGARG The error should occur when you try to delete the date table named "Table" in attached .pbix file. As to calculate PreMon use the PARALLELPERIOD() function, the table "Table" is necessary. 

PreMon = CALCULATE(SUM(Table1[Qty]),PARALLELPERIOD('Table1'[Date],-1,MONTH))

Best Regards,
Qiuyun Yu

[CahabaData · Accepted solution]

actually the intent of my input/question is that: this post is in Service but your advised solution involves a calculated column - which I think can only be done in Desktop.  I was trying to clarify this point.

[PKGARG]

Hi,

Great Job....

Thanks

[CahabaData]

Qiuyun Yu, are we in Desktop for this?