Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
arifulice
Helper I
Helper I

cumulative sum for some particular months

Please help me for cumulaitve value for Particular month like January to May

1 ACCEPTED SOLUTION

@arifulice

 

I am surprised to see this post still open. Did you try to use my solution? It is a straightforward cumulative DAX pattern with one additional statement to account for month of May logic you want.  Give it a try.


RT Jan to May =
CALCULATE (
    [Sales Amount],
    FILTER (
        ALL ( 'Date' ),
        'Date'[Date] <= MAX ( 'Date'[Date] )
            && MAX ( 'Date'[Date] ) <= DATE ( YEAR ( MAX ( 'Date'[Date] ) ), 4, 30 )
              && YEAR ( 'Date'[Date] ) = YEAR ( MAX ( 'Date'[Date] ) )
    )
)

 

View solution in original post

20 REPLIES 20
Anonymous
Not applicable

@arifulice

 

Have you tried with any formula before? How is your data stored?

 

 

Hello Martin,

Thanks for your replay, I have tried by the following formula

SimpleCumulative:=CALCULATE(SUM(Sales[SalesAmount]),FILTER(ALL('Calendar'[EnglishMonthName]),
'Calendar'[EnglishMonthName]<=MAX('Calendar'[EnglishMonthName])),VALUES('Calendar'[Year]))

The formula giving  the result of all month but i need only the result from January to May  Month?

Hi @arifulice

 

It turned out to be a pretty tricky question even though it appears to be an easy one.  
Looking at the code below you will immediately recognize the standard DAX for calculating running totals. However, it's the bolded part that helps to retun the logic that you want.

What we say here is that the max dates in the filter context must be less or equal to the last day of the month of May of the year in the current filter context.

 

 

RT Jan to May =
CALCULATE (
    [Sales Amount],
    FILTER (
        ALL ( 'Date' ),
        'Date'[Date] <= MAX ( 'Date'[Date] )
            && MAX ( 'Date'[Date] ) <= DATE ( YEAR ( MAX ( 'Date'[Date] ) ), 5, 31 )
              && YEAR ( 'Date'[Date] ) = YEAR ( MAX ( 'Date'[Date] ) )
    )
)

image.png

Thanks, Nick

Below my code :

 

SimpleCumulative:=CALCULATE(SUM(Sales[SalesAmount]),FILTER(ALL('Calendar'),
'Calendar'[EnglishMonthName]<=MAX('Calendar'[EnglishMonthName])))

The result of the code is belowCapture.PNG

But i want to get result only first four months..

@arifulice

 

I noticed your months are sorted alphabetically  and not by month number. This has to be done in the first place otherwise none of your measures will work properly. 

 

Also, you mentioned that now you want to return 4 months of running total. To get that reflected with my code, you need to change the month number and a date as shown below in the part marked in bold.

 

 

RT Jan to May =
CALCULATE (
    [Sales Amount],
    FILTER (
        ALL ( 'Date' ),
        'Date'[Date] <= MAX ( 'Date'[Date] )
            && MAX ( 'Date'[Date] ) <= DATE ( YEAR ( MAX ( 'Date'[Date] ) ), 4, 30 )
              && YEAR ( 'Date'[Date] ) = YEAR ( MAX ( 'Date'[Date] ) )
    )
)

 

Without manual entry i want to get my 2010(Jan to Mar) month sales datat.

 

Is possiable can we do in power bi ?

@AmalrajRRD1

 

If I understood you correctly you are looking to see your sales only from Jan 2010 to Mar 2010.

Sales Jan To Mar =
CALCULATE (
    [Sales Amount],
    FILTER (
    	Date,
    	Date[Date] >= DATE(2010, 1,1)
    		&& Date[Date] <= DATE(2010, 3, 31)
    )
)



Nick -

Ya right . I don t want 2010,1,1 bez every month i am updateing the dashboard i don t like to modify other thing so i need everything by a function . 

For example Now i have April 2010 dataset . for your reference again i need to go query and modify that then only it s working .

bez beforely we write the query between Jan 2010 to Mar 2010.

 

But i don t want do again and again .

 

Did you got my questions?

 

 

Thanks

 

 

@AmalrajRRD1

 

First of all.  Your new question is not relative to the topic of this forum post. Could you please start a new forum post and let's start understanding what is your goal in a new post.  Please tag my name @nickchobotar  so i know what's posted. I would like to help you with this.

 

If possible,   please show the result you expect to get visually in a table.

 

N -

Hi AmalrajRRD1 ,

What do you mean by manual entry. Describe briefly your problem

Thanks

 

Anonymous
Not applicable

 @arifulice

 

Try this out then:

 

Make a column which determines what number of month you are in. Then go for this DAX formula

 

SimpleCumulative:=IF('Calender'[EnglishMonthNameNUMBER]=<4;CALCULATE(SUM(Sales[SalesAmount]),FILTER(ALL('Calendar'),
'Calendar'[EnglishMonthName]<=MAX('Calendar'[EnglishMonthName])));0)

Let me know if it makes sense and if it works. 

Hi ValubiMartin ,

 

I have tried by your given formila but it not working properly giving error

My formula 

 

 


SimpleCumulative:=IF('Calendar'[MonthNumber]<=4,CALCULATE(SUM(Sales[SalesAmount]),FILTER(ALL('Calendar'), 'Calendar'[MonthNumber]<=MAX('Calendar'[MonthNumber]))),0)

 

 

 

 

@arifulice,

 

Can you post the error message when using ValubiMartin's formula? Besides, nickchobotar's solution should also work, have you ever try it?

 

Regards,

Charlie Liao

Hi  v-caliao-msft,

The Error when using ValubiMartin formula are given below 

 

"MonthNumber in table calendar can not be determined.This can be happen when a measure formula refers to a column that contains many values without specifying  an aggegation such as min, max,count....."

 

 

 

Thanks

ArifulIce

@arifulice

 

I am surprised to see this post still open. Did you try to use my solution? It is a straightforward cumulative DAX pattern with one additional statement to account for month of May logic you want.  Give it a try.


RT Jan to May =
CALCULATE (
    [Sales Amount],
    FILTER (
        ALL ( 'Date' ),
        'Date'[Date] <= MAX ( 'Date'[Date] )
            && MAX ( 'Date'[Date] ) <= DATE ( YEAR ( MAX ( 'Date'[Date] ) ), 4, 30 )
              && YEAR ( 'Date'[Date] ) = YEAR ( MAX ( 'Date'[Date] ) )
    )
)

 

Hi @nickchobotar,

 

I'm using the below DAX formula for Cumulative count

 

Actual Cumulative = SUMX(FILTER(ALLSELECTED('Calendar'[Date]),'Calendar'[Date]<=MAX('Calendar'[Date])),[Actual Delivery Date])

 

But, I required cumulative count till current month (Date) not for future months (Dates).

 

Can u please help me out

 

Thanks.

 

 

 

Thanks nickchobotar  for your help. Smiley Happy..  I have tried to solve my problem by using your formula witout sorting month.After sorting the problem is solved

Very good!!!

Anonymous
Not applicable

@arifulice

 

The solution developed by @nickchobotar and the one developed by me are very different, and they both depend on how your data is stored and what conditions you want to impose on the calculation. So if you could elaborate on why you need to make the calculation based on certain months, it would be really helpful.

Anonymous
Not applicable

Hi @arifulice

 

 Can you show me the visual, where you are plotting your data?

 

I've solved a similar problem before, where the solution was this:

 

Cumulative total of Value = CALCULATE(SUM('Table'[Value]);FILTER(ALL('Table'[Date]); 'Table'[Date] <= MAX ( 'Table'[Date] )))

 

This assumes that your date column is of type date. Below you can see a picture of my solution. Let me know how it goes for you.

 

If this doesn't work out for you i need to know more about how your data is stored.

 

Running total with missing months.png

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.