Skip to main content
cancel
Showing results for 
Search instead 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

Reply
raellaylo
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? 

raellaylo_0-1686054477434.png

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.   

raellaylo_2-1686054672322.png

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
raellaylo
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/2023ABC123Drum setSingapore        12,162.70
6/30/2023DEF456Guitar SetSingapore        23,834.25
3/31/2023ABC123Drum setSingapore        12,164.80
3/31/2023DEF456Guitar SetSingapore        23,817.45
12/31/2022ABC123Drum setSingapore        12,192.10
12/31/2022DEF456Guitar SetSingapore        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)
 

raellaylo_1-1686676729103.png

 

 

My desired output would be something like:

raellaylo_2-1686676823193.png

 

 

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

miTutorials
Super User
Super User

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

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

Mahesh0016
Super User
Super User

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

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

raellaylo_0-1686059150369.png

 

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. 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors