topic Re: I wanna show the monthly change in % - with a DAX Formula in Power BI in DAX Commands and Tips

I wanna show the monthly change in % - with a DAX Formula in Power BI

LTi — Wed, 21 Feb 2024 13:42:45 GMT

what am I doing wrong? I get an Error when I try it like this:

% Veränderung Monat =

IF(

ISFILTERED('Profit Share gesamt'[offerAcceptedDate2]),

ERROR("Quickmeasures mit Zeitintelligenz können nur über die von Power BI bereitgestellte Datumshierarchie oder die primäre Datumsspalte gruppiert oder gefiltert werden."),

VAR _PREV_MONTH =

CALCULATE(

SUM('Profit Share gesamt'[Profit]),

DATEADD('Profit Share gesamt'[offerAcceptedDate2].[Monat], -1, MONTH

RETURN

SUM('Profit Share gesamt'[Profit])-_PREV_MONTH

)

Hope you can help me!

Best, TL

Re: I wanna show the monthly change in % - with a DAX Formula in Power BI

lbendlin — Thu, 22 Feb 2024 00:39:55 GMT

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: I wanna show the monthly change in % - with a DAX Formula in Power BI

Anonymous — Thu, 22 Feb 2024 07:59:03 GMT

Hi LTi ,

Hope all is going well.

Based on your description, I found that the formula was missing the closing bracket of the function and failed to calculate the percentage change correctly.

Here are my adjusted measure, please try:

% Veränderung Monat = IF( ISFILTERED('Profit Share gesamt'[offerAcceptedDate2]), BLANK(), VAR _Current_Month_Profit = SUM('Profit Share gesamt'[Profit]) VAR _PREV_MONTH_Profit = CALCULATE( SUM('Profit Share gesamt'[Profit]), DATEADD('Profit Share gesamt'[offerAcceptedDate2], -1, MONTH) ) VAR _Change = _Current_Month_Profit - _PREV_MONTH_Profit RETURN IF( _PREV_MONTH_Profit = 0, BLANK(), DIVIDE(_Change, _PREV_MONTH_Profit) ) )

If your problem cannot be solved successfully, please provide me with sample data for testing, and be careful not to include private information.

Thank you for your patience and waiting.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

Re: I wanna show the monthly change in % - with a DAX Formula in Power BI

LTi — Thu, 22 Feb 2024 11:15:35 GMT

Unternehmens ID	Closed am	offerAcceptedDate2	Sales MA	Sales Team Name	Unternehmensname	Umsatz	Profit	Kalenderwoche (calender week)	Kalenderjahr (calendar year)
1	04.01.2023	04.01.2023	Max Muster	Sales Team 1	Unternehmen A	2.611,00 €	132	1	2023
2	05.01.2023	05.01.2023	Daniel Friedrich	Sales Team 2	Unternehmen B	2.799,00 €	341	1	2023
3	05.01.2023	05.01.2023	Michelle Mayer	Sales Team 3	Unternehmen C	2.799,00 €	280	1	2023
4	10.02.2023	10.02.2023	Max Muster	Sales Team 1	Unternehmen D	2.302,00 €	132	6	2023
5	11.02.2023	11.02.2023	Michelle Mayer	Sales Team 3	Unternehmen E	2.799,00 €	160	6	2023

Re: I wanna show the monthly change in % - with a DAX Formula in Power BI

LTi — Thu, 22 Feb 2024 11:23:26 GMT

Hi Yang, thanks for your advise but I received an error message with a note to the DateAdd. I've uploaded an example table but it is shown absolutely unformatted. I used the table insert feature from the menue bar.

Is there also an Option to upload an excel.file as well? Thanks for your support and Sorry for my bad attachment 🙂

Re: I wanna show the monthly change in % - with a DAX Formula in Power BI

Anonymous — Fri, 23 Feb 2024 02:40:27 GMT

Hi LTi ,

Thank you very much for your reply, which is very helpful for us to solve the problem smoothly.

I tested it based on the example data you provided and found that the error appeared on the ISFILTERED function.

This function is judged on the table or column and returns a polymerization value. However, when we calculate%, we need to calculate it one by one. At this time, the result of IsfilterEred is always a true, so the next step will not be carried out.

I made some changes to this, please follow these steps:

1. First of all, let's create a measure of the profit of the current month:

Current_Month_Profit = CALCULATE(SUM('Profit Share gesamt'[Profit]),MAX('Profit Share gesamt'[offerAcceptedDate2]) = 'Profit Share gesamt'[offerAcceptedDate2])

2. Create a measuling value of calculating the profit of the previous month:

_PREV_MONTH_Profit = var a = CALCULATE( SUM('Profit Share gesamt'[Profit]), DATEADD('Profit Share gesamt'[offerAcceptedDate2], -1, MONTH) ) RETURN IF(a = 0,0,a)

3. Create a measuring value that calculates the difference between them:

_Change = [Current_Month_Profit] - [_PREV_MONTH_Profit]

4. Create a measure value for%calculation:

% Veränderung Monat = FORMAT( DIVIDE([_Change],[_PREV_MONTH_Profit]), "Percent")

5. Finally, drag these created measurement values to the report page to display:

If you have other questions, please contact me at any time, thank you for your patience and cooperation.

The pbix file has been attached.

Best Regards,
Yang
Community Support Team

Re: I wanna show the monthly change in % - with a DAX Formula in Power BI

LTi — Wed, 28 Feb 2024 11:49:04 GMT

Hi Yang, sorry for my late response & thank you very much for your effort and your great support. Unfortunately, the measures do not yet lead to the correct results. Maybe I forgot to mention this in advance, but I work with the data slice function in power BI and if I filter on the sales employee and on a certain month then I should be able to see the profit per the selected employee in the respective month and then ideally compare it with the profit from the previous month. You should also be able to see the percentage profit share of the total profit for the selected employee and also the average growth rate of the profit. Your first measure works correctly but don't show me the correct profit value when I filter month and sales employee. Your second measure threw an error message with a note to the used DateAdd function. What can be the problem?