Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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
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.
hi @Ivan_lopez
avoid use VAR here, try like
CALCULATE(SUM(), DATEADD())
Varible in DAX is more like a constant: once defined, never changes.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |