cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Cynthia
Regular Visitor

difference between data _ months in same column

Hi,

In powerBI desktop I am preparing a report in which i need compare the difference month by month. in my source data the month are in the same column, so I can't use simple difference function. the data are as below. and i need create difference between feb-jan, mar-feb, apr-mar and so on. How I better do it? thanks a lot in advance. (I am showing in excel, cause I can't share my report in powerbi, it is just to exemplify the issue) 

Cynthia_0-1642352032037.png

Cynthia_1-1642352068166.png

 

2 ACCEPTED SOLUTIONS
littlemojopuppy
Super User
Super User

Hi @Cynthia.  In order for this to work, you need to do three things first:

  1. Make sure your data model has a date table and that it is marked as one
  2. Convert your values of "Jan 21", "oct 21", etc. to actual date values.  Go with month ending values.
  3. Create a relationship between the date field in the date table and the date field in your data.

After you do those things, create measures like the following

Current Month = SUM([Value])

Prior Month = 
CALCULATE(
	[Current Month]
	PREVIOUSMONTH([Use the name of the date column from the date table])
)

MTM Difference = [Current Month] - [Prior Month]

 

Hope this helps!

View solution in original post

Hi @Cynthia 

 

I created the solution for you.  Follow the logic in the attached pbix.  Let me know if you have any questions.  🙂

View solution in original post

13 REPLIES 13
Cynthia
Regular Visitor

Hi @littlemojopuppy ,

 

it worked! Thank you so much. I have accepted as solution.

 

Kind regards,

 

Cynthia 

Hi @Cynthia all I did in the pbix was implement the suggestion I offered with creating date table, converting your text fields to actual dates, etc.  That was the real solution 🙂  I marked that as the solution.

Thank you very much @littlemojopuppy 

@Cynthia you're welcome.  Glad I could help!

v-henryk-mstf
Community Support
Community Support

Hi @Cynthia ,

 

If you want to create formulas based on measure, the solution provided by @littlemojopuppy  works. If there are still problems, please point it out.


Looking forward to your feedback.


Best Regards,
Henry


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thank you very much @v-henryk-mstf .

v-henryk-mstf
Community Support
Community Support

Hi @Cynthia ,

 

According to your description, refer to the following formula. If you don't want to categorize the item column, just delete 'Table'[Category] = EARLIER ( 'Table'[Category] ) from the formula.

Col_ = 
VAR current_ =
    MAX ( 'Table'[Date] )
VAR next_ =
    CALCULATE (
        MAX ( 'Table'[Value] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Date] > EARLIER ( 'Table'[Date] )
                && 'Table'[Category] = EARLIER ( 'Table'[Category] )
        )
    )
RETURN
    next_ - 'Table'[Value]

vhenrykmstf_0-1642559554369.png

Also note that the data field needs to be changed to a date type.

vhenrykmstf_1-1642559588158.png


If the problem is still not resolved, please point it out. Looking forward to your reply.


Best Regards,
Henry


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-henryk-mstf ,

 

first, thank you so much for support and patience. I am not sure what was the issue, but when replacing the data it was still not leading to the results needed. Maybe I did something wrong. Maybe it is also related to the hierarchy levels that I have in category. I am a beginner, so I am not really sure. The proposal from user littlemojopuppy using measures worked. So I have accepted as solution.

 

Again, thank you very very much.

 

Kind regards,

 

Cynthia

Hi @v-henryk-mstf 

 

first of all, thank you so much for your support and I apologize for late reply. Usually I have weekends for these tasks. 

 

I have tried the suggestion you have sent me, but it is not returning the difference between feb-jan, then march-feb and so on. I could not identify yet which calculation it is actually doing. I believe I have chosen the correct table fields from my report to replace in the code you sent me.

 

Below I am forwarding the headers of the report, maybe it is more clarifying. I will be grateful if you could still support.

I need calculate the difference between act. cost in feb minus jan, then march-feb, then apr-march and so on. As in the screen shot below. 

Screenshot.jpg

 

PREVIEW
Thank you again.
Kind regards,
 
Cinthia
 
 

@v-henryk-mstf with all due respect, this solution doesn't remediate having a bad data model.

There is an inverse relationship between quality of the data model and the complexity of code to accommodate it.  I suggested changing the data model to a more normalized relational structure and could solve the issue with three very simple measures totalling six lines of DAX.  Your proposed solution - to this issue only - 12 lines of DAX.  What happens with the user needs additional measures?

 

The underlying issue is the data model and unnecessary complexity in it.  Fix that, this is a slam dunk.  This forum shouldn't be just solving immediate problems...this should also be teaching as well.  🙂

littlemojopuppy
Super User
Super User

Hi @Cynthia.  In order for this to work, you need to do three things first:

  1. Make sure your data model has a date table and that it is marked as one
  2. Convert your values of "Jan 21", "oct 21", etc. to actual date values.  Go with month ending values.
  3. Create a relationship between the date field in the date table and the date field in your data.

After you do those things, create measures like the following

Current Month = SUM([Value])

Prior Month = 
CALCULATE(
	[Current Month]
	PREVIOUSMONTH([Use the name of the date column from the date table])
)

MTM Difference = [Current Month] - [Prior Month]

 

Hope this helps!

Hi @littlemojopuppy ,

 

firstly, thank you very much for your support and apologies for my late response. I work on it on weekends.

 

I have tried to apply your suggestion but without success. I will still try to find the error I am probably doing when following the reasoning. I have just started. I will give you a more assertive response if I find the mistake when replacing the data.

 

Thank you very much.

 

Kind regards,

 

Cinthia

Hi @Cynthia 

 

I created the solution for you.  Follow the logic in the attached pbix.  Let me know if you have any questions.  🙂

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Dev Camp Session 22 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!