Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
ccueto36
Frequent Visitor

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.

3 ACCEPTED SOLUTIONS
Zubair_Muhammad
Community Champion
Community Champion

@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] ) )

View solution in original post

@ccueto36

 

When I use it with a sample table it works

Please see Power Pivot model in attached Excel Workbook

 

 

lkup3.png

View solution in original post

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:

1.PNG

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):

2.PNG

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

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
v-danhe-msft
Microsoft Employee
Microsoft Employee

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

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Zubair_Muhammad
Community Champion
Community Champion

@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] ) )

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

 

When I use it with a sample table it works

Please see Power Pivot model in attached Excel Workbook

 

 

lkup3.png

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:

1.PNG

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):

2.PNG

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

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.