Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear fellows,
I would like to calculate a KPI (growth). Example: I would like to sum up the pieces for 2017 as far as pieces for 2018 exist and compare them.
So in Fiscal Year 2018 there are pieces from accounting month 201710 until accounting month 201807 (yellow),
so I would like to compare them with pieces from Fiscal Year 2017 from accounting month 201610 until 201707 (green).
The yellow numbers should be Sum({<FiscalYear = {2018}>} [Pieces])
but I don't get the green formular.
Any idea how I could manage that with a formular?
Many thanks for any support!! Best, Markus
Hi,
for green cells, why don't you use this:
Sum({<FiscalYear = {2017}>} [Pieces])
Hi Youssef,
thank you for answering!
if I would use "Sum({<FiscalYear = {2017}>} [Pieces])" I would also add the pieces of accounting Month 201708 and 201709.
But I only want to sum up the month until 201707, to compare them with the numbers of Fiscal Year 18 (201710-201807)
Thank you!
Can u share a little excel sheet or an inline table so that we can work on it?
This can be easily achieved through set analysis but all depends on the fields u have.
Please share source.
And there is a thing I can't understand, how is the year 2017 associated to Months 201610,201611 and 201612?
Anyway, Here's my solution:
1) add a date field:
fiscal:
load * , MonthEnd(date#(AccountingMonth,'YYYYMM')) as AccountingDate Inline [
Pieces, FiscalYear,AccountingMonth
5,2017,201610
20,2017,201611
30,2017,201612
40,2017,201701
10,2017,201702
22,2017,201703
53,2017,201704
40,2017,201705
5,2017,201706
0,2017,201707
4,2017,201708
6,2017,201709
5, 2018,201710
2,2018,201711
16,2018,201712
50,2018,201801
60,2018,201802
50,2018,201803
10,2018,201804
30,2018,201805
10,2018,201806
20,2018,201807
];
2) Use this:
Yellow:
sum({<AccountingDate,AccountingMonth,FiscalYear, FiscalYear={"$(=max(FiscalYear))"}, AccountingDate={"<=$(=max(AccountingDate))"}>}Pieces)
Green:
sum({<AccountingDate,AccountingMonth,FiscalYear, FiscalYear={"$(=max(FiscalYear)-1)"}, AccountingDate={"<=$(=AddYears(max(AccountingDate),-1))"}>}Pieces)
Result:
Thank you so much, it finally works!
In our company the fiscal year always starts in October, that's why... so FY 17 is 201610 - 201709 and so on...