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

New Member

## %monthly change

Dear all,

I have a request for you, would you help me in creating a monthly percentage change?

For example I have 3 companies, each company have some total value for each month (period) and account. So first I made a new column as "End of the Month" - date type. The table you can see below

First, I created a measure for Total Amount

TOTAL_AMOUNT = SUM (TEST[TOTAL VALUE])

This KPI works with the "Company" filter which means that if I selected "Company" A and "Period" 1 in the filters, the total sum is 250.

Then I created a second measure for Previous Month Value:
PREVIOUS_MONTH_VALUE = CALCULATE([TOTAL_AMOUNT], PARALLELPERIOD(TEST[END OF THE MONTH], -1,MONTH))

And this is probably where the problem arises. Because if I filter only period 2, I don't see any values in the "Previous Value" column in the table. However, if I select all companies and all periods, I get the total value minus the total value for period 3 in the total.

I would need the metric to work by showing me the monthly difference between within each company and then the % difference displayed using the KPI visual, which I assume is a fairly simple metric in the form of

%MONTHLY_CHANGE = DIVIDE([TOTAL_AMOUNT] - [PREVIOUS_MONTH_VALUE], [PREVIOUS_MONTH_VALUE])

My idea is something like this:

Please advise me how to achieve this. I have tried different types of filters but nothing has shown me the results I would like.

Thank you very much in advance

1 ACCEPTED SOLUTION
Super User

@tomba_96 what measure are you using for the previous month?

``PREVIOUS_MONTH_VALUE = CALCULATE([TOTAL_AMOUNT], DATEADD ( 'Calendar'[Date], -1,MONTH))``

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

5 REPLIES 5
Super User

@tomba_96 sounds good. Mark the reply as a solution so that others can get help from it. Cheer!

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Super User

@tomba_96 what measure are you using for the previous month?

``PREVIOUS_MONTH_VALUE = CALCULATE([TOTAL_AMOUNT], DATEADD ( 'Calendar'[Date], -1,MONTH))``

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

New Member

Thank you, I've already figured out that I have to use DATEADD. Thank you again

New Member

I have created a list of dates using List.Dates function in M language, which I then converted as a table and added columns with start of month, end of month,....

But still, after selecting the filter and the period, the total value in the last month for that company is not displayed

What am I still doing wrong?

Thank you

Super User

As a best practice, add a date dimension in your model and use it for time intelligence calculations. Once the date dimension is added, mark it as a date table on table tools. Check the related videos on my YT channel

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

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

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors