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
RobbyLorenz3004
Regular Visitor

Calculation of the percentage difference from the previous day

Hello dear community. I would like to determine the percentage deviation from the previous day. As an example: On October 9th, 2023 we had 15,000 pieces. On October 10th, 2023 we had processed 20,000 pieces. That's a percentage deviation/change of 33%. How can I implement this using Measure DAX in the Power Bi Desktop? Also that this continues automatically after the import. So the same calculation for October 10th and 11th, etc. Thank you in advance. The text was created using Google Translate because I don't speak English very well.

1 ACCEPTED SOLUTION

Hi @RobbyLorenz3004 ,

 

Here I create a sample to have a test.

vrzhoumsft_1-1697449289499.png

I think you can try code as below to create a measure.

Deviation =
VAR _ADD1 =
    ADDCOLUMNS (
        SUMMARIZE (
            'Table',
            'Table'[Date],
            "Quantity Today", CALCULATE ( SUM ( 'Table'[Quantity] ) )
        ),
        "Quantity Yesterday",
            CALCULATE (
                SUM ( 'Table'[Quantity] ),
                FILTER ( ALL ( 'Table' ), 'Table'[Date] = EARLIER ( [Date] ) - 1 )
            )
    )
VAR _ADD2 =
    ADDCOLUMNS (
        _ADD1,
        "Deviation",
            IF (
                [Quantity Yesterday] = BLANK (),
                BLANK (),
                DIVIDE (
                    [Quantity Today] - [Quantity Yesterday],
                    ( [Quantity Today] + [Quantity Yesterday] ) / 2
                )
            )
    )
RETURN
    SUMX ( _ADD2, [Deviation] ) + 0

Result is as below.

vrzhoumsft_2-1697449335795.png

 

Best Regards,
Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

View solution in original post

5 REPLIES 5
RobbyLorenz3004
Regular Visitor

image.png

Hi @RobbyLorenz3004 ,

 

Here I create a sample to have a test.

vrzhoumsft_1-1697449289499.png

I think you can try code as below to create a measure.

Deviation =
VAR _ADD1 =
    ADDCOLUMNS (
        SUMMARIZE (
            'Table',
            'Table'[Date],
            "Quantity Today", CALCULATE ( SUM ( 'Table'[Quantity] ) )
        ),
        "Quantity Yesterday",
            CALCULATE (
                SUM ( 'Table'[Quantity] ),
                FILTER ( ALL ( 'Table' ), 'Table'[Date] = EARLIER ( [Date] ) - 1 )
            )
    )
VAR _ADD2 =
    ADDCOLUMNS (
        _ADD1,
        "Deviation",
            IF (
                [Quantity Yesterday] = BLANK (),
                BLANK (),
                DIVIDE (
                    [Quantity Today] - [Quantity Yesterday],
                    ( [Quantity Today] + [Quantity Yesterday] ) / 2
                )
            )
    )
RETURN
    SUMX ( _ADD2, [Deviation] ) + 0

Result is as below.

vrzhoumsft_2-1697449335795.png

 

Best Regards,
Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

RobbyLorenz3004
Regular Visitor

Hello some bi,

First of all, thank you, because there is now a calculation. Unfortunately, the end results don't add up. The aim is to show a deviation (in %) between the quantity from yesterday and today. As an example: Yesterday we had a quantity of 10,571 pieces. Today a quantity of 14,243 pieces. This results in a deviation of +29.60%. According to your calculation it comes to 137.93%. I selected "Percentage of total value of column" in the Measure setting. I also found a formula (Google) that determines a difference in percent.

Formula:
((14243 - 10571) / ((10571 + 14243) /2 ) * 100 | = 29.6%

Can you help me here??

Hi @RobbyLorenz3004 

it seems feasible.

please show your example in table / column view so it can be easy undersand. Output is also great, if formula like yours should we applied let us know.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






some_bih
Super User
Super User

Hi @RobbyLorenz3004 possible solution is measure as below

Measure_test =
VAR __current_date = MAX('<yourtablename>'[Date])
VAR __current_pieces = MAX('<yourtablename>'[Pieces])
VAR __previous_date = CALCULATE(MAX('<yourtablename>'[Date]), FILTER(ALL('<yourtablename>'), '<yourtablename>'[Date] < __current_date))
VAR __previous_pieces = CALCULATE(MAX('<yourtablename>'[Pieces]), FILTER(ALL('<yourtablename>'), '<yourtablename>'[Date] = __previous_date))
VAR __Result=DIVIDE(__current_pieces - __previous_pieces, __previous_pieces, 0)
RETURN __Result

 

Did I answer your question? Kudos appreciated / accept solutio





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






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.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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