Forum Discussion

Cynthia's avatar
Cynthia
Regular Visitor
4 years ago
Solved

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. t...
Need Help
  • littlemojopuppy's avatar
    littlemojopuppy
    4 years ago

    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!

  • littlemojopuppy's avatar
    littlemojopuppy
    4 years ago

    Hi Cynthia 

     

    I created the solution for you.  Follow the logic in the attached pbix.  Let me know if you have any questions.  ğŸ™‚

v-henryk-mstf's avatar
v-henryk-mstf
Community Support
4 years ago

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]

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


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.

littlemojopuppy's avatar
littlemojopuppy
Community Champion
4 years ago

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.  ğŸ™‚