cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Thomas94401
Frequent Visitor

Cumulative sum sales monthly

Hi, 

 

I got an issue with computing cumulative sum sales monthly. 

 

I'm using a slicer which defines my periode : 

- M1 will be the first month 

- M2 : the second month ...

The architecture is the following : 

Thomas94401_0-1635196633303.png

with : 
- a consumption table

- 2 Date tables with columns : Date and EndMonth
- 1 disconnected table defines as follows : 

Thomas94401_1-1635196754345.png

So i compute month by month the sum of consumption as follows : 

SOMME_CONSO_P1 =

VAR SlicerMon =
MIN(Mois[DIFF])

VAR EOMMinSlicerDate =
EOMONTH(MIN(Date_survenance_P1[DATE]), SlicerMon)

RETURN
CALCULATE(
SUM('Conso par mois'[CONSO]),
Date_survenance_P1[FIN_MOIS] = EOMMinSlicerDate
)
 
Thomas94401_2-1635196886587.png

 

And I would like to compute, then, the cumulative sum of consumption but I obtain the same values as SOMME_CONSO_P1

CUMUL_CONSO_P1 =

VAR SlicerMon =
MIN(Mois[DIFF])

VAR EOMMinSlicerDate =
EOMONTH(MIN(Date_survenance_P1[DATE]), SlicerMon)

RETURN
CALCULATE(
[SOMME_CONSO_P1],
FILTER(
ALLSELECTED(Date_survenance_P1[DATE]),
Date_survenance_P1[DATE] <= MAX(Date_survenance_P1[DATE])
)
)
 
Thanks in advance !

 



2 REPLIES 2
Eyelyn9
Community Support
Community Support

Hi @Thomas94401 ,

 

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution to make the thread closed. More people will benefit from it.

 

Best Regards,
Eyelyn Qin

Eyelyn9
Community Support
Community Support

Hi @Thomas94401 ,

 

1. How to get this:

VAR SlicerMon = MIN(Mois[DIFF])

 The DIFF is the MonthsAway column in this table? Or a new column?

Eyelyn9_0-1635386713324.png

 

2. Based on my simple data sample

Eyelyn9_1-1635387045430.png

If select M4 , the MonthsAway is 3, then below syntax will return 2021/10/31, which is not exist in my

Date_survenance table, so the measure will return Blank. 
EOMONTH(MIN(Date_survenance_P1[DATE]), SlicerMon)

Eyelyn9_2-1635387281996.png

 

 

Not sure if your issue is the same since there is no data ...

So could you please share more detail information(Tables, expected outputs...)or share me with your pbix file after removing sensitive data to help us clarify your scenario?

 

 

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.