cancel
Showing results for
Did you mean: Frequent Visitor

## Calculate cumulative sum of last year

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:

1. Mesures on Qlik Sense
• AFN_N_CUMUL

AFN_N_CUMUL = rangesum(above(sum({\$ <periode_annee= {\$(vAnnee)}>
}cpt_AN*\$(=(val_affichage))),0,12))

• AFN_N-1_CUMUL

AFN_N-1_CUMUL = rangesum(above(sum({\$ <periode_annee= {\$(=vAnneePrec)}>
}cpt_AN*\$(=(val_affichage))),0,12))​

• AFN_N

AFN_N = sum({\$ <periode_annee= {\$(vAnnee)}> }cpt_AN*\$(=(val_affichage)))

• AFN_N-1

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

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

AFN_N_CUMUL =
CALCULATE([AFN_N],
FILTER(ALL(Periode[periode_mois]),
Periode[periode_mois] <= MAX(Periode[periode_mois])
))

• AFN_N-1

AFN_N-1 = CALCULATE([AFN_N], ALLEXCEPT(sto_co,sto_co[periode]), SAMEPERIODLASTYEAR(Periode[debut]))

I have tried to create AFN_N-1_CUMUL like below but it doesn't work, the result is always Null.

AFN_N-1_CUMUL = CALCULATE([AFN_N-1], FILTER(ALLSELECTED(Periode[periode_mois]),
Periode[periode_mois] <= MAX(Periode[periode_mois])
))

P/s: I will post some informations related to this problem below, hope this help to understand problem better:
- Model dimension - Table Periode - Table sto_co to (for counting): Thanks,

H.A

1 ACCEPTED SOLUTION  Community Support

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.

11 REPLIES 11  Super User

You can use SAMEPERIODLASTYEAR or PARALLELPERIOD.  They both return the full prior period.  What issue do you see with SAMEPERIODLASTYEAR ? Frequent Visitor

But I have to mention that I need to create CUMULATIVE SUM of LAST YEAR, not SUM of LAST YEAR.  Super User Frequent Visitor

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.  Super User

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. Frequent Visitor

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: AFN_N_CUMUL_Test = TOTALYTD(AFN[AFN_N],Periode[fin])

Btw, what I need now is how to create a measure that display the CUMULATIVE SUM of LAST YEAR on monthly granularity 😢  Community Support

Hi @ha_data

I think you can try to calculate AFN_N-1_CUM by sumx, and filter year = year-1&&date<=Max(Date).

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. Frequent Visitor

Hi @RicoZhou ,

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 :

AFN_N-1_CUMUL = CALCULATE([AFN_N],
FILTER(Periode, (Periode[periode_annee] = Periode[periode_annee] - 1 ) && Periode[periode_mois] <= MAX(Periode[periode_mois]))
)
So I will send you a sample of my report to help you understand more about data model and its issue through my Onedrive for Business : https://eciliafr-my.sharepoint.com/:u:/g/personal/hnguyen_ecilia_fr/EagJJIkHk8tCot3ZaVfPg2wBTPbfoG3O...
Best regards,
H.A  Community Support

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. Frequent Visitor

Thank you so much, finally it works. Frequent Visitor

I have used it like below but it returned NULL:

• AFN_N-1_CUMUL = CALCULATE([AFN_N-1], PARALLELPERIOD(Periode[debut],-1,YEAR))
• AFN_N-1_CUMUL = CALCULATE([AFN_N-1], SAMEPERIODLASTYEAR(Periode[debut]))  