Forum Discussion

sam_hoccane's avatar
sam_hoccane
Icon for Helper I rankHelper I
4 years ago
Solved

adding new rows in table with condition

Hello Community, 

I am new user to Power BI. Can you please help me with following problem:

I have following table where three rows needs to added.  So after May 22, I would like to add three more lines: June 22, July 22 and August 22 taking the previous month's values and multiplying by 1.2... 

So that means, for 22-June I will take previous month value (i.e. 90 for May) and multiple it by 1.2 and July I will take value of June and multiply it by 1.2.

It would be great if you provide solution in DAX  but power querry also work for me... 

Expected results should look like this :

Year Month 

Value

22-Mar

60

22-Apr

80

22-May

90

22-Jun

108

22-Jul

129.6

22-Aug

155.52

DAX
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    4 years ago

    Hi sam_hoccane ,

    According to your description, I create a sample.

    Here's my solution.

    1.Create a new table. Make relationship with the two tables, the Year Month column in the two tables both should be of date type.

    2.Create a measure.

    Measure Value =
IF (
    MAX ( 'Table'[Value] ) <> BLANK (),
    MAX ( 'Table'[Value] ),
    MAXX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[Year Month ] = MAXX ( ALL ( 'Table' ), 'Table'[Year Month ] )
        ),
        'Table'[Value]
    )
        * POWER (
            1.2,
            DATEDIFF (
                MAXX ( ALL ( 'Table' ), 'Table'[Year Month ] ),
                MAX ( 'Table (2)'[Year Month] ),
                MONTH
            )
        )
)

    Get the result.

    I attach my sample below for reference.

     

    Best Regards,
    Community Support Team _ kalyj

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

     

3 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    4 years ago

    Hi,

    I am not sure if I understood your question correctly, but I tried to do it in Power Query Editor.

    All steps are shown in PQ Editor in the attached file.

    Thank you.

     

    • sam_hoccane's avatar
      sam_hoccane
      Icon for Helper I rankHelper I
      4 years ago

      Jihwan_Kim thankyou so much for the reply. 

       This is my given data : 

      Year Month 

      Value

      22-Mar

      60

      22-Apr

      80

      22-May

      90

       

      I want to calculate three rows for June, July and August months. So for the june,  I take the Value from May and multiply it with 1.2  (i.e 90 *1.2 = 180) . For july, I take calculated value from  June and multiply it with 1.2 (i.e.  180*1.2= 129.6) and for August I am  taking calculated value from July and multiply it with 1.2 (i.e. 129.6*1.2 ).  Is it possible is  DAX ? if not than Power Querry is fine

       

      so final result should look like this :

      Year Month 

      Value

      22-Mar

      60

      22-Apr

      80

      22-May

      90

      22-Jun

      108

      22-Jul

      129.6

      22-Aug

      155.52

       

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi sam_hoccane ,

    According to your description, I create a sample.

    Here's my solution.

    1.Create a new table. Make relationship with the two tables, the Year Month column in the two tables both should be of date type.

    2.Create a measure.

    Measure Value =
IF (
    MAX ( 'Table'[Value] ) <> BLANK (),
    MAX ( 'Table'[Value] ),
    MAXX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[Year Month ] = MAXX ( ALL ( 'Table' ), 'Table'[Year Month ] )
        ),
        'Table'[Value]
    )
        * POWER (
            1.2,
            DATEDIFF (
                MAXX ( ALL ( 'Table' ), 'Table'[Year Month ] ),
                MAX ( 'Table (2)'[Year Month] ),
                MONTH
            )
        )
)

    Get the result.

    I attach my sample below for reference.

     

    Best Regards,
    Community Support Team _ kalyj

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