cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Calculated column that shows the value from the previous quarter

I have a per quarter record of costs. Is there an easy way to create a calculated column that would return the previous quarter's value?

I am able to do it via calculated measure, and was able to calculate differences between quarters.  There's no issue when I try to show the total net difference.  However, when I try to show the total of all increases only (or all decreases only), I can't get it to show the correct total value.

Hence, I am thinking it might be easier to calculate it if I am able to use calculated columns instead of calculated measures.  If there's any suggestion to do this without needing to do calculated columns, that would be much welcomed too.

Thanks in advance for any help.

6 REPLIES 6
Frequent Visitor

I can't attach my pbix nor my excel file, maybe due to privacy settings.  But here's the sample table I am working with:

 Quarter Date Code Product Description Country Cost 6/30/2023 ABC123 Drum set Singapore 12,162.70 6/30/2023 DEF456 Guitar Set Singapore 23,834.25 3/31/2023 ABC123 Drum set Singapore 12,164.80 3/31/2023 DEF456 Guitar Set Singapore 23,817.45 12/31/2022 ABC123 Drum set Singapore 12,192.10 12/31/2022 DEF456 Guitar Set Singapore 23,771.25

I have tried researching on this for the past week or so, but haven't seen one that seems to apply to what I need.  So really need someone's help.

Here's a snip of what my Power BI looks like when I added some measures. Here are the formula I used.  I have actually tried more than 10 diff't measures using EARLIER functions, VAR, DATEADD, etc.. but to no avail. So I did not inlcude them here, so as to not be confusing :

_ValuePrevQ = CALCULATE(SUM(Data[Cost]), PREVIOUSQUARTER(Data[Quarter Date]))

_DiffThisQvsPrevQ = CALCULATE(SUM(Data[Cost])) - [_ValuePrevQ]

_NetDecrease = IF([_DiffThisQvsPrevQ] < 0, [_DiffThisQvsPrevQ], 0)

_NetIncrease = IF([_DiffThisQvsPrevQ] > 0, [_DiffThisQvsPrevQ], 0)

My desired output would be something like:

thanks in advance for any one who might be able to support.

Super User
Watch this quick tutorial to fix incorrect total in your measures. Hope this helps. https://www.youtube.com/watch?v=J2FTRqrx0m0
Frequent Visitor

Thanks a lot for this, but this didn't seem to resolve my concern.

Super User

@raellaylo Please can you share your ENDOUTPUT in table for better understanding.

Frequent Visitor

Something like this.  I want to be able to see how much are the total increases, and separately how much are the total decreases:

Frequent Visitor

I am thinking that if I could at least get to create a calculated column that returns the value from the previous quarter, then I'd be able to do the necessary IF formula to weed out the increases from the decreases.  But that's where I'm stuck.