cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## Month value from Runing total

Hello Community,

How can I extract month value if I have runing total to each month in year and  ID?

I know how to do it in Excel:

``````=if(
mid([PERIOD];6;2)=1;[@RUNING_TOTAL];
SUMPRODUCT([RUNING_TOTAL];1*([ID_PRODUCT]=[@ID]);1*([ID_REGION]=[@ID_REGION]);1*(mid([PERIOD];6;2)=(mid([@PERIOD];6;2)-1)))
)``````

How can I do it in DAX, I have no idea.

Table example:

 ID_PRODUCT RUNNING TOTAL ID_REGION PERIOD 1 4468569 03 2013-01 1 13181477 03 2013-02 1 18057210 03 2013-03 1 23813848 03 2013-04 1 31506587 03 2013-05 1 37021277 03 2013-06 1 40547668 03 2013-07 1 48832654 03 2013-08 1 52329409 03 2013-09 1 55873990 03 2013-10 1 64148323 03 2013-11 1 70567919 03 2013-12 1 78418626 03 2014-12 1 69989454 03 2014-11 1 61181832 03 2014-10 1 56631864 03 2014-09 1 50089694 03 2014-08 1 41740839 03 2014-07 1 36893009 03 2014-06 1 30716642 03 2014-05 1 23231056 03 2014-04 1 17173236 03 2014-03 1 12137904 03 2014-02 1 3438783 03 2014-01

1 ACCEPTED SOLUTION

Accepted Solutions
Super User IV

## Re: Month value from Runing total

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
8 REPLIES 8
Resolver II

## Re: Month value from Runing total

You need first to get the running sum value for previous month and then subtract "previous month running sum" from "current month running sum" to get each month's value.

Running Sum of Previous Month:

``Prev.Month Running Sum Value = CALCULATE( max('Table'[RUNNING TOTAL]), PREVIOUSMONTH('Table'[PERIOD]))``

Current Month Value:

``Current Month Value = CALCULATE(max('Table'[RUNNING TOTAL])) - [Prev.Month Running Sum Value]``

Here is the example file I was using: Sample File

Best Regards,

Haitham

Community Support

## Re: Month value from Runing total

Could you please clarify the logic of the expression below?

``  SUMPRODUCT([RUNING_TOTAL];1*([ID_PRODUCT]=[@ID]);1*([ID_REGION]=[@ID_REGION]);1*(mid([PERIOD];6;2)=(mid([@PERIOD];6;2)-1)))``

Community Support Team _ Jimmy Tao

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

Super User IV

## Re: Month value from Runing total

Hi,

Based on the dataset that you have shared, please show the expected result.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Frequent Visitor

## Re: Month value from Runing total

In my example it will be the same as SUMIFS in excel after 2013. In general it helps multiply values with list of conditions.

For example:

We need multiply [Value_1]*[Value_2] where [Condition_1]="Value 1:&&[Condition_2]="Type 5"

 Condition_1 Condition_2 Value_1 Value_2 Value 1 Type 5 4 2 Value 2 Type 2 5 8 Value 1 Type 1 3 7 Value 3 Type 4 4 2 Value 4 Type 5 1 1

The expression will be:

``=SUMPRODUCT(1*([Condition_1]="Value 1]);1*([Condition_2]="Type 5");[Value_1];[Value_2])``

As a result we will get matrix for multiply

 Condition_1 Condition_2 Value_1 Value_2 1 (Value 1 = Value 1 ► TRUE) 1 (Type 5 = Type 5 ► TRUE) 4 2 8 (1*1*4*2) 0 (Value 2 <> Value 1 ► FALSE) 0 5 8 0 (0*0*5*8) 1 (Value 1 = Value 1 ► TRUE) 0 3 7 0 (1*0*3*7) 0 (Value 3 <> Value 1 ► TRUE) 0 4 2 0 (0*0*4*2) 0 (Value 4 <> Value 1 ► TRUE) 1 1 1 0 (0*1*1*1) RESULT: 8 (8+0+0+0+0)

Frequent Visitor

## Re: Month value from Runing total

Hi. As a result I would like to get a table:

 ID_PRODUCT RUNING_TOTAL ID_REGION PERIOD YYYY MM TOTAL_MONTH 1 4 468 569 3 2013-01 2013 1 4 468 569 1 13 181 477 3 2013-02 2013 2 8 712 908 1 18 057 210 3 2013-03 2013 3 4 875 733 1 23 813 848 3 2013-04 2013 4 5 756 638 1 31 506 587 3 2013-05 2013 5 7 692 739 1 37 021 277 3 2013-06 2013 6 5 514 690 1 40 547 668 3 2013-07 2013 7 3 526 391 1 48 832 654 3 2013-08 2013 8 8 284 986 1 52 329 409 3 2013-09 2013 9 3 496 755 1 55 873 990 3 2013-10 2013 10 3 544 581 1 64 148 323 3 2013-11 2013 11 8 274 333 1 70 567 919 3 2013-12 2013 12 6 419 596 1 78 418 626 3 2014-12 2014 12 8 429 172 1 69 989 454 3 2014-11 2014 11 8 807 622 1 61 181 832 3 2014-10 2014 10 4 549 968 1 56 631 864 3 2014-09 2014 9 6 542 170 1 50 089 694 3 2014-08 2014 8 8 348 855 1 41 740 839 3 2014-07 2014 7 4 847 830 1 36 893 009 3 2014-06 2014 6 6 176 367 1 30 716 642 3 2014-05 2014 5 7 485 586 1 23 231 056 3 2014-04 2014 4 6 057 820 1 17 173 236 3 2014-03 2014 3 5 035 332 1 12 137 904 3 2014-02 2014 2 8 699 121 1 3 438 783 3 2014-01 2014 1 3 438 783

And I found that expression above was not final. It should have been:

``=IF([@MM]=1;[@[RUNNING TOTAL]];[@[RUNNING TOTAL]]-SUMPRODUCT([RUNNING TOTAL];1*([ID_PRODUCT]=[@[ID_PRODUCT]]);1*([ID_REGION]=[@[ID_REGION]]);1*([YYYY]=[@YYYY]);1*([MM]=([@MM]-1))))``

Super User IV

## Re: Month value from Runing total

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Frequent Visitor

## Re: Month value from Runing total

Thanks for your help. It's that I need.

Super User IV

## Re: Month value from Runing total

You are welcome.

Regards,
Ashish Mathur
http://www.ashishmathur.com

Announcements

#### New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

#### ‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

#### Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors