Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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

 

 

 

Need Help

8 Replies

  • helassal's avatar
    helassal
    Icon for Resolver II rankResolver II
    6 years ago

    Anonymous ,

     

    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

  • v-yuta-msft's avatar
    v-yuta-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Anonymous ,

     

    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.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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)

       

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    6 years ago

    Hi,

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

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