I am trying to compare the sales of one fiscal year with another previous fiscal year. All these fiscal years must be totaled to date according to the month of the current fiscal year.
As you will see in the following image, the current fiscal year (FY18) is until the month of February 2018.
For this reason, the other fiscal years must be calculated until the corresponding month of February.
AF16, until February 2016; 6.530.678.992,00
AF17, until February 2017; 22.577.217.970,00
I am using the following measure, (It is important to note that for the use of dates in this measure, I do not use the table of dates because the result would be ANNUAL TOTALS).
mPrevious Previous =
CALCULATE (SUM (DATA [Bs.]); PARALLELPERIOD (DATA [Date]; - 1; MONTH)),
which works well with the previous fiscal years, but with the current period I have problems, since the calculation in the current fiscal year is calculated until the month of January and should be until February
AF18 should be 207,616,960,489.00
All this is because I need to calculate the% of Growth of a fiscal year with respect to the other fiscal years.
% growth = (current fiscal year-previous fiscal year) / current fiscal year.
And according to this formula, this should give me the following results according to the image
As always, thank you all for your valuable help
Because there isn't a date context in the visual, the context of date is the whole dates of the Fiscal years respectively. But the fiscal year AF18 lacks a month comparing to other two fiscal years. The result of "mPrevious Previous" is a total of data from 01/04/2017 to 10/01/2018. You will see the result of AF18 is 175030472733 that is 207616960489 - 32586487756(Bs. of date 11/02/2018. )
The easiest solution is adding a record of 12/03/2018 like this:
AF18 12/03/2018 (blank or 0)
Good morning v-jiascu-msft.
Thanks for your help but the table is until February 2018 because it is the period in which the records were so far. I can not place March 2018 in Zero or in blank because when the records are March 2018, what will happen with that condition?
Hi my friends.
I want to know if is possible that you can help me to resolve this issue that I have with this table.
I want that the value -18,09 to be in the row of the year 2018 and not in the year 2017. The value -183,18 to be in the row of the year 2017 and not in the year 2016, and in the year 2016 have to be 100.
Some like this:
Those are the formulas:
AcumulaPeriodos Bs =
CuentaMeses = CALCULATE(DISTINCTCOUNT(DATA[Mes]);DATA[AÑO FISCAL]="2018")
DiferenciaMeses= 12 - CuentaMeses
IF(DATA[AñoMax]="2018";CALCULATE([mTOTALVENTAS Bs.];DATA[AÑO FISCAL]="2018");CALCULATE(SUM(DATA[Bs.]);PARALLELPERIOD(DATA[Fecha];-DiferenciaMeses;MONTH)))
IF(DATA[AñoMax]="2018";(CALCULATE([mTOTALVENTAS Bs.];DATA[AÑO FISCAL]="2018")-[mPeriodoAnterior])/[mPeriodoAnterior];([mPeriodoActual2]-[mPeriodoAnterior])/[mPeriodoAnterior])
mPeriodoAnterior = CALCULATE(SUM(DATA[Bs.]);PARALLELPERIOD(DATA[Fecha];-1;MONTH);ALLEXCEPT(DATA;DATA[AÑO FISCAL]))
mPeriodoActual2 = CALCULATE([mTOTALVENTAS Bs.];DATA[AÑO FISCAL]="2018")