Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
LTi
Frequent Visitor

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

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
1 ACCEPTED 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:

vhuijieymsft_0-1708655828505.png

 

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!

View solution in original post

6 REPLIES 6
LTi
Frequent Visitor

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?

v-huijiey-msft
Community Support
Community Support

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 IDClosed amofferAcceptedDate2Sales MASales Team NameUnternehmensnameUmsatzProfitKalenderwoche (calender week)Kalenderjahr (calendar year)
104.01.202304.01.2023Max MusterSales Team 1Unternehmen A    2.611,00 €13212023
205.01.202305.01.2023Daniel FriedrichSales Team 2Unternehmen B    2.799,00 €34112023
305.01.202305.01.2023Michelle MayerSales Team 3Unternehmen C    2.799,00 €28012023
410.02.202310.02.2023Max MusterSales Team 1Unternehmen D    2.302,00 €13262023
511.02.202311.02.2023Michelle MayerSales Team 3Unternehmen E    2.799,00 €16062023

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:

vhuijieymsft_0-1708655828505.png

 

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!

lbendlin
Super User
Super User

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

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors