cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Calculate the difference between values not blank

Hi Guys,
I need to calculate the difference between column (3)

Last value not blank this month  - Last value not blank before this moth

 

Calculo Odometro - Copia.png

2 REPLIES 2
Nathaniel_C
Super User
Super User

Hi @Anonymous 
Try this: The code looks longer than it is.  Merely duplicate for each different month, in this case Jan and February. Will point out the high non blank value in January is not the last date, whereas the last nonblank value in February is the last date in February, but it is not the highest value, which is correct.
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel
lno feb.PNG

 




LastNonBlank Value of Feb - Jan =
VAR LastNonBlankDateJanuary =
    CALCULATE (
        MAX ( myTable[Date] ),
        FILTER (
            ALL ( myTable ),
            myTable[Date] <= DATE ( 2019, 1, 31 )
                && myTable[Date] >= DATE ( 2019, 1, 1 )
                && myTable[Value] <> 0
        )
    )
VAR _January =
    CALCULATE (
        SUM ( myTable[Value] ),
        FILTER ( ALL ( myTable ), myTable[Date] = LastNonBlankDateJanuary )
    )
VAR LastNonBlankDateFebruary =
    CALCULATE (
        MAX ( myTable[Date] ),
        FILTER (
            ALL ( myTable ),
            myTable[Date] <= DATE ( 2019, 2, 28 )
                && myTable[Date] >= DATE ( 2019, 2, 1 )
                && myTable[Value] <> 0
        )
    )
VAR _February =
    CALCULATE (
        SUM ( myTable[Value] ),
        FILTER ( ALL ( myTable ), myTable[Date] = LastNonBlankDateFebruary )
    )
RETURN
    _February - _January


 





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

Proud to be a Super User!




Anonymous
Not applicable

@Nathaniel_C thanks  for the help, I need a flexible solution.
I developed the solution below, but this calculate is very slow:
Do you know a form of optimizer?

Delta KM com VAR = 
//------------------------------------------------------------------------
SUMX(
    VALUES(Diesel[Chave]);
 //-------------------Primeira Quilomatragem----------------------------------------------
VAR Primeiro_KM = 
    CALCULATE(
        if(
        CALCULATE(
        max(Diesel[Quilometragem ou Horas]);
        FILTER(
            all(Diesel);
            Diesel[Data da Transacao] < min(Diesel[Data da Transacao])&&
            Diesel[Quilometragem ou Horas] <> BLANK()&&
            Diesel[Inventario] = max(Diesel[Inventario])
        )
    ) =BLANK();
        CALCULATE(
            max(Diesel[Quilometragem ou Horas]);
            FILTER(
                all(Diesel);
                Diesel[Data da Transacao] <= min(Diesel[Data da Transacao])&&
                Diesel[Quilometragem ou Horas] <> BLANK()&&
                Diesel[Inventario] = max(Diesel[Inventario])
            )
        );
        CALCULATE(
            max(Diesel[Quilometragem ou Horas]);
            FILTER(
                all(Diesel);
                Diesel[Data da Transacao] < min(Diesel[Data da Transacao])&&
                Diesel[Quilometragem ou Horas] <> BLANK()&&
                Diesel[Inventario] = max(Diesel[Inventario])
            )
        )
    )
)
//------------------------Ultima Quilomatragem----------------------------------------------------
    VAR Ultimo_KM = 
    CALCULATE(
        CALCULATE(
        max(Diesel[Quilometragem ou Horas]);

            LASTNONBLANK(
                Diesel[Data da Transacao];
                CALCULATE(max(Diesel[Quilometragem ou Horas])
                )
            )   
)
    )
    return
 //------------------------------------------------------------------------\

    Ultimo_KM-Primeiro_KM
)

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors