delay registration for one year

[Ivan_lopez]

Hi,

I am trying to shift the "DEF PREMIUMS" data by one year. The final premiums are calculated as follows:

DEF_Prem2021 = PROV_Prem_2022 - (PROV_Prem_2022 * Growth)

For this reason I need to shift the final premiums by one year and for the last year to show PROV_Prem_2022.

I have tried to perform this transformation using PowerQuery but it has not been possible. I have created the following function:

Expected final result

I would appreciate any help.

Thank you in advance, 

Iván.

[v-yiruan-msft]

Hi @Ivan_lopez ,

Could you please provide some raw data in your fact table(exclude sensitive data)? Are the field [Year], [Prov] and [Grotwh] from the same table 'Fact Total'? If the matrix involves any measure, please also provide the related formula. Thank you.

Best Regards

[Ivan_lopez]

I need to do the following calculations:

- YEAR > 2021: Def Premiums 2021 = Prov_Prem_2022 - (Prov_Prem_2022 * Growth) and for the last year indicate that Def_Prov2022 = Def_Prem2022.

- YEAR <= 2020: Def Premiums 2020 = Def Premiums 2020

I have tried with both calculated columns and DAX metrics but I have not been able to

Año	Cod_DGS	Crecimiento	DGS_AÑO_Key	Sum of Primas emitidas (PROV)	Sum of Primas emitidas (DEF)	Sum of Primas emitidas	Sum of Primas Def Año Ant
1/1/2019 0:00	C0157	0	C0157-2020	449177899.2	449177899.2	449.1778992	449.1778992
1/1/2020 0:00	C0157	0	C0157-2020	451616876.7	451616876.7	451.6168767	451.6168767
1/1/2021 0:00	C0157	0.031898588	C0157-2021	466022817.4	0	466.0228174	451.1573475
1/1/2022 0:00	C0157	0.05	C0157-2022	495323958.3	0	495.3239583	470.5577604

Best Regards,

Iván.

[FreemanZ]

hi @Ivan_lopez 

avoid use VAR here, try like

CALCULATE(SUM(), DATEADD())

Varible in DAX is more like a constant: once defined, never changes.