Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. 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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
146 | |
75 | |
64 | |
52 | |
47 |
User | Count |
---|---|
218 | |
88 | |
73 | |
64 | |
60 |