Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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.
Solved! Go to Solution.
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
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
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
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 😕
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
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
92 | |
86 | |
85 | |
67 | |
49 |
User | Count |
---|---|
139 | |
113 | |
104 | |
64 | |
60 |