Month value from Runing total

[DenisSlav]

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

[Ashish_Mathur]

Hi,

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

[DenisSlav]

@Ashish_Mathur 

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

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[DenisSlav]

@Ashish_Mathur 

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

[Ashish_Mathur]

You are welcome.

[v-yuta-msft]

@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.

[DenisSlav]

@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_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)

[helassal]

@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]

Here is the example file I was using: Sample File 

Best Regards,

Haitham