Calculation of the percentage difference from the previous day

[RobbyLorenz3004]

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.

[RobbyLorenz3004]

[v-rzhou-msft · Accepted solution]

Hi @RobbyLorenz3004 ,

Here I create a sample to have a test.

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.

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]

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

[some_bih]

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.

[some_bih]

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