Forum Discussion
Offsetting Calculated Column Rows
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
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
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 :/
