Forum Discussion
difference between data _ months in same column
Hi Cynthia. In order for this to work, you need to do three things first:
- Make sure your data model has a date table and that it is marked as one
- Convert your values of "Jan 21", "oct 21", etc. to actual date values. Go with month ending values.
- 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 Cynthia
I created the solution for you. Follow the logic in the attached pbix. Let me know if you have any questions. 🙂
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.
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