Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
what am I doing wrong? I get an Error when I try it like this:
Solved! Go to Solution.
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
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!
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?
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!
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 🙂
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 |
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
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!
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-...
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/1447...
User | Count |
---|---|
21 | |
19 | |
12 | |
10 | |
9 |
User | Count |
---|---|
30 | |
26 | |
15 | |
13 | |
10 |