cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
DenisSlav
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_PRODUCTRUNNING TOTALID_REGIONPERIOD
14468569032013-01
113181477032013-02
118057210032013-03
123813848032013-04
131506587032013-05
137021277032013-06
140547668032013-07
148832654032013-08
152329409032013-09
155873990032013-10
164148323032013-11
170567919032013-12
178418626032014-12
169989454032014-11
161181832032014-10
156631864032014-09
150089694032014-08
141740839032014-07
136893009032014-06
130716642032014-05
123231056032014-04
117173236032014-03
112137904032014-02
13438783032014-01

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User IV
Super User IV

Re: Month value from Runing total

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

8 REPLIES 8
helassal Resolver II
Resolver II

Re: Month value from Runing total

@DenisSlav ,

 

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]

 

CurrentMonthFromRunningSum.jpg

 

Here is the example file I was using: Sample File 

 

Best Regards,

Haitham

Community Support
Community Support

Re: Month value from Runing total

@DenisSlav ,

 

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
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
https://www.linkedin.com/in/excelenthusiasts/
DenisSlav
Frequent Visitor

Re: Month value from Runing total

@v-yuta-msft ,

 

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_1Condition_2Value_1Value_2
Value 1Type 542
Value 2Type 258
Value 1Type 137
Value 3Type 442
Value 4Type 511

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_1Condition_2Value_1Value_2 
1 (Value 1 = Value 1 ► TRUE)1 (Type 5 = Type 5 ► TRUE)428 (1*1*4*2)
0 (Value 2 <> Value 1 ► FALSE)0580 (0*0*5*8)
1 (Value 1 = Value 1 ► TRUE)0370 (1*0*3*7)
0 (Value 3 <> Value 1 ► TRUE)0420 (0*0*4*2)
0 (Value 4 <> Value 1 ► TRUE)1110 (0*1*1*1)
   RESULT:8 (8+0+0+0+0)

 

DenisSlav
Frequent Visitor

Re: Month value from Runing total

@Ashish_Mathur 

 

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

ID_PRODUCTRUNING_TOTALID_REGIONPERIODYYYYMMTOTAL_MONTH
14 468 56932013-01201314 468 569
113 181 47732013-02201328 712 908
118 057 21032013-03201334 875 733
123 813 84832013-04201345 756 638
131 506 58732013-05201357 692 739
137 021 27732013-06201365 514 690
140 547 66832013-07201373 526 391
148 832 65432013-08201388 284 986
152 329 40932013-09201393 496 755
155 873 99032013-102013103 544 581
164 148 32332013-112013118 274 333
170 567 91932013-122013126 419 596
178 418 62632014-122014128 429 172
169 989 45432014-112014118 807 622
161 181 83232014-102014104 549 968
156 631 86432014-09201496 542 170
150 089 69432014-08201488 348 855
141 740 83932014-07201474 847 830
136 893 00932014-06201466 176 367
130 716 64232014-05201457 485 586
123 231 05632014-04201446 057 820
117 173 23632014-03201435 035 332
112 137 90432014-02201428 699 121
13 438 78332014-01201413 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
Super User IV

Re: Month value from Runing total

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

DenisSlav
Frequent Visitor

Re: Month value from Runing total

@Ashish_Mathur 

 

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

Super User IV
Super User IV

Re: Month value from Runing total

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
New Ranks Launched March 24th!

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!

‘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

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