Forum Discussion
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 column is exactly the same numbers as Sum of chargeAmt except that it's offset by 2 months behind. I have no idea where to begin to be able to generate that new column and make the first 2 rows blank like that. I need that column in order to generate Cash Factor. I'm new to the Power BI and DAX world so I would appreciate the help.
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] ) )When I use it with a sample table it works
Please see Power Pivot model in attached Excel Workbook
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
6 Replies
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] ) )- 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?
When I use it with a sample table it works
Please see Power Pivot model in attached Excel Workbook
Hi ccueto36,
Could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered?
Regards,
Daniel He
