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.
Hi Guys,
I need to calculate the difference between column (3)
Last value not blank this month - Last value not blank before this moth
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
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
Proud to be a Super User!
@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
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
18 |