cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ha_data
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])
))
 
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
ha_data_0-1629366950869.png
- Table Periode
ha_data_1-1629366990098.png

 

- Table sto_co to (for counting):

ha_data_2-1629367096196.png

Thanks,

H.A

1 ACCEPTED SOLUTION
RicoZhou
Community Support
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.

1.png

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.

View solution in original post

11 REPLIES 11
lbendlin
Super User
Super User

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

ha_data
Frequent Visitor

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

please explain the difference.

ha_data
Frequent Visitor

I will use this graph to explain the difference:

ha_data_0-1629467395024.png

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.

ha_data
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:

ha_data_0-1629469654685.png

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 😢
RicoZhou
Community Support
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).

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
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
RicoZhou
Community Support
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.

1.png

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.

View solution in original post

ha_data
Frequent Visitor

Thank you so much, finally it works.

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

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors