Forum Discussion

ccueto36's avatar
ccueto36
Frequent Visitor
8 years ago
Solved

Offsetting Calculated Column Rows

  Hi guys, I'm having a bit of a dilemma with generating a Calculated Column in DAX, the goal is to calculate Cal_charge+Amt column from the Excel table above. As you can see, the data on that ...
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    ccueto36

     

    Try this calculated column

     

    Cal_charge+Amt =
VAR temp =
    TOPN (
        2,
        FILTER ( Table3, [Month-Year] < EARLIER ( [Month-Year] ) ),
        [Month-Year], DESC
    )
VAR temp1 =
    TOPN ( 1, temp, [Month-Year], ASC )
RETURN
    IF ( COUNTROWS ( temp ) = 2, MINX ( temp1, [Sum of ChargeAmt] ) )
  • v-danhe-msft's avatar
    v-danhe-msft
    8 years ago

    Hi ccueto36,

    Based on my test, I have tried what Zubair suggested, it could work on my side, and you could also refer to my step:

    Sample data:

    Create a calculated column:

    Column 2 = var a=MONTH('Table3'[Month-Year])-2
return CALCULATE(SUM(Table3[Sum of ChargeAmt]),FILTER('Table3',MONTH('Table3'[Month-Year])=a))

    Result(Column1 is the Zubair's function):

    You could also download the pbix to have a view, if it still could not work, could you please share the pbix if possible?

    https://www.dropbox.com/s/jjdh69fw2ipjbr4/Offsetting%20Calculated%20Column%20Rows.pbix?dl=0

     

     

    Regards,

    Daniel He

ccueto36's avatar
ccueto36
Frequent Visitor
8 years ago
I've been trying this since yesterday but to no avail. I'm getting a fully blank calculated column as a result every time I try it. If I take off the IF(COUNTROWS(temp1) = 2 condition, then it's not blank anymore, just random numbers that isn't the expected answer. On a side note, Table3 is the table name where all of the above columns are from?
  • ccueto36's avatar
    ccueto36
    Frequent Visitor
    8 years ago

    Thank you for the sample, I can also make it work just fine in Excel Power Pivot but the same does not seem to work in Power BI Calculated Column, keeps returning blank column :/

    • v-danhe-msft's avatar
      v-danhe-msft
      Microsoft Employee
      8 years ago

      Hi ccueto36,

      Based on my test, I have tried what Zubair suggested, it could work on my side, and you could also refer to my step:

      Sample data:

      Create a calculated column:

      Column 2 = var a=MONTH('Table3'[Month-Year])-2
return CALCULATE(SUM(Table3[Sum of ChargeAmt]),FILTER('Table3',MONTH('Table3'[Month-Year])=a))

      Result(Column1 is the Zubair's function):

      You could also download the pbix to have a view, if it still could not work, could you please share the pbix if possible?

      https://www.dropbox.com/s/jjdh69fw2ipjbr4/Offsetting%20Calculated%20Column%20Rows.pbix?dl=0

       

       

      Regards,

      Daniel He