cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jbolivar Frequent Visitor
Frequent Visitor

Total Previous Fiscal year vs Actual fiscal year

Hello everyone.
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.TablaVentasPeriodoFiscal.JPG

 

 

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

 

GraficoPBI_Periodos.JPG

 

 

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

tablaresultadoesperado.JPG

 

 

As always, thank you all for your valuable help

 

 

 

3 REPLIES 3
Highlighted
Community Support Team
Community Support Team

Re: Total Previous Fiscal year vs Actual fiscal year

Hi @jbolivar,

 

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)

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
jbolivar Frequent Visitor
Frequent Visitor

Re: Total Previous Fiscal year vs Actual fiscal year

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?

jbolivar Frequent Visitor
Frequent Visitor

Re: Total Previous Fiscal year vs Actual fiscal year

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.

Tabla1.JPG

 

 

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:tablaresultadoesperado.JPG

Those are the formulas:

 

AcumulaPeriodos Bs
VAR
CuentaMeses = CALCULATE(DISTINCTCOUNT(DATA[Mes]);DATA[AÑO FISCAL]="2018")
VAR
DiferenciaMeses= 12 - CuentaMeses
RETURN
IF(DATA[AñoMax]="2018";CALCULATE([mTOTALVENTAS Bs.];DATA[AÑO FISCAL]="2018");CALCULATE(SUM(DATA[Bs.]);PARALLELPERIOD(DATA[Fecha];-DiferenciaMeses;MONTH)))

 

m%Crecimiento2
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")

 

Thank you