Calculate cumulative sum of last year

[ha_data]

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])
))

Could someone please help me on this problem? Any help would be greatly appreciated.

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

[lbendlin]

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

[ha_data]

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

[lbendlin]

please explain the difference.

[ha_data]

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. 

[lbendlin]

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.

[ha_data]

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 😢

[v-rzhou-msft]

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.

[ha_data]

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 :

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

[v-rzhou-msft · Accepted 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.

[ha_data]

Thank you so much, finally it works.

[ha_data]

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]))