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

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
Ashish_Mathur
Super User
Super User

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/

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

 

 

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/

@Ashish_Mathur 

 

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

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-yuta-msft
Community Support
Community Support

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

 

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

 

helassal
Resolver II
Resolver II

@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

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.