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
tomba_96
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

 

tomba_96_0-1693069162934.png

 

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.
 
tomba_96_1-1693071044874.png

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:
tomba_96_2-1693071302885.png

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
parry2k
Super User
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.

View solution in original post

5 REPLIES 5
parry2k
Super User
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.

parry2k
Super User
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.

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

tomba_96
New Member

Thank you for your reply.

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,....

tomba_96_0-1693113739009.png

 

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

tomba_96_1-1693113980554.png

 

What am I still doing wrong?

 

Thank you

parry2k
Super User
Super User

@tomba_96 

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

 

Add Date Dimension
Importance of Date Dimension
Time Intelligence Playlist

 



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.

Helpful resources

Announcements
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

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