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 have been stucked a day to figure out how to create a measure to calculate cumulative sum of last year.
More precisely, I'm trying to replicate some measures of Qlik Sense on Power BI. Below are some mesures of Qlik Sense and some mesures of Power BI that I have created successfully:
AFN_N_CUMUL = rangesum(above(sum({$ <periode_annee= {$(vAnnee)}>
}cpt_AN*$(=(val_affichage))),0,12))
AFN_N-1_CUMUL = rangesum(above(sum({$ <periode_annee= {$(=vAnneePrec)}>
}cpt_AN*$(=(val_affichage))),0,12))
AFN_N = sum({$ <periode_annee= {$(vAnnee)}> }cpt_AN*$(=(val_affichage)))
AFN_N-1 = sum({$ <periode_annee= {$(vAnneePrec)}> }cpt_AN*$(=(val_affichage)))
2. I have created successfully AFN_N, AFN_N-1, AFN_N_CUMUL on Power BI but I have lost with mesure AFN_N-1_CUMUL (a measure to calculate cumulative sum of last year):
AFN_N = IF(ISCROSSFILTERED(Affichage_dnb[lib_affichage]),
SWITCH(TRUE(),
VALUES(Affichage_dnb[lib_affichage]) = "en Nombre", SUMX(sto_co, sto_co[cpt_AN] * 1),
VALUES(Affichage_dnb[lib_affichage]) = "en Commission", SUMX(sto_co, sto_co[cpt_AN] * RELATED(Polices_devis[Comm_Annuelle])),
VALUES(Affichage_dnb[lib_affichage]) = "en PTTC annuelle", SUMX(sto_co, sto_co[cpt_AN] * RELATED(Polices_devis[PTTC_Annuelle])),
VALUES(Affichage_dnb[lib_affichage]) = "en PHT annuelle", SUMX(sto_co, sto_co[cpt_AN] * RELATED(Polices_devis[PHT_Annuelle])),
BLANK()), BLANK())
AFN_N_CUMUL =
CALCULATE([AFN_N],
FILTER(ALL(Periode[periode_mois]),
Periode[periode_mois] <= MAX(Periode[periode_mois])
))
AFN_N-1 = CALCULATE([AFN_N], ALLEXCEPT(sto_co,sto_co[periode]), SAMEPERIODLASTYEAR(Periode[debut]))
- Table sto_co to (for counting):
Thanks,
H.A
Solved! Go to Solution.
Hi @ha_data
Try this measure.
AFN_N-1_CUMUL =
CALCULATE (
[AFN_N-1],
FILTER (
ALL ( Periode[periode_mois] ),
Periode[periode_mois] <= MAX ( Periode[periode_mois] )
)
)
Result is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
You can use SAMEPERIODLASTYEAR or PARALLELPERIOD. They both return the full prior period. What issue do you see with SAMEPERIODLASTYEAR ?
But I have to mention that I need to create CUMULATIVE SUM of LAST YEAR, not SUM of LAST YEAR.
please explain the difference.
I will use this graph to explain the difference:
As you see, AFN_N is sum of the year selected, AFN_N-1 is sum of previous year, and AFN_N_CUMUL is cumulative sum of the year selected. Now I want to create AFN_N-1_CUMUL which is cumulative sum of previous year. The cumulative sum will calculate the total from January to a month that you select or to the end of a year, while if you use sum, it will only calculate sum of each month in this graph.
You neglected to mention that your chart has monthly granularity. If it would display the years only then there would be no difference.
For cumulative measures on lower levels you can use the built in functions like TOTALYTD or DATESYTD.
I don't know why but these functions (TOTALYTD or DATESYTD...) don't work in my case. For example, I create a function AFN_N_CUMUL_Test and it works like a sum, not cumulative sum like you can see it below:
Hi @ha_data
I think you can try to calculate AFN_N-1_CUM by sumx, and filter year = year-1&&date<=Max(Date).
If this reply still couldn't help you solve your problem, please share an easy sample without sensitive data with me by your Onedrive for Business. And you can show me the result you want with screenshot.
This may make it easier for me to understand your data model and to find the issue.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-rzhou-msft ,
Thank for your respond. However, I have tried to implement the mesure AFN_N-1_CUMUL as you sugguested as below but it still returned nothing :
Hi @ha_data
Try this measure.
AFN_N-1_CUMUL =
CALCULATE (
[AFN_N-1],
FILTER (
ALL ( Periode[periode_mois] ),
Periode[periode_mois] <= MAX ( Periode[periode_mois] )
)
)
Result is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you so much, finally it works.
I have used it like below but it returned NULL:
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 |
---|---|
13 | |
2 | |
2 | |
1 | |
1 |