I have the following two codes. The first one works as long as all of the levels in the same group have the same date.
The secode code just doesn't quite work right.
LastTotalDayTankLevel =
VAR SelectedTank =
SELECTEDVALUE ( Tanks[Tank_Name] )
VAR SelectedGenerator =
LOOKUPVALUE (
Tanks[fk_GeneratorName_ID],
Tanks[Tank_Name],
SelectedTank
)
VAR IBXID =
LOOKUPVALUE (
IBXs[IBX_ID],
IBXs[IBX_Name],
SELECTEDVALUE ( IBXs[IBX_Name] )
)
VAR LastLevelDate =
CALCULATE (
MAX (
RunData[RunData_Date]
),
ALLEXCEPT (
Tanks,
Tanks[Tank_Name]
),
Tanks[Tank_Name] = SelectedTank,
IBXs[IBX_ID] = IBXID
)
RETURN
IF (
ISINSCOPE ( Tanks[Tank_Name] ),
CALCULATE (
LASTNONBLANK ( RunData[DT_Level], 1 ),
RunData[fk_GeneratorName_ID] = SelectedGenerator,
RunData[RunData_Date] = LastLevelDate
),
SUMX (
SELECTCOLUMNS (
FILTER (
ALL ( Tanks ),
[fk_IBX_ID] = IBXID
),
"tID",
[fk_GeneratorName_ID]
),
CALCULATE (
LASTNONBLANK ( RunData[DT_Level], 1 ),
FILTER (
RunData,
AND (
RunData[RunData_Date] = LastLevelDate,
AND (
RunData[fk_IBX_ID] = IBXID,
RunData[fk_GeneratorName_ID] = [tID]
)
)
)
)
)
)LastTotalDTLevel =
VAR IBXID =
LOOKUPVALUE (
IBXs[IBX_ID],
IBXs[IBX_Name],
SELECTEDVALUE ( IBXs[IBX_Name] )
)
VAR DT1Date =
CALCULATE (
MAX (
RunData[RunData_Date]
),
ALLEXCEPT (
Tanks,
Tanks[Day_Tank],
Tanks[INDEX]
),
RunData[fk_IBX_ID] = IBXID,
Tanks[Day_Tank] = TRUE(),
Tanks[INDEX] = 1
)
VAR DT2Date =
CALCULATE (
MAX (
RunData[RunData_Date]
),
ALLEXCEPT (
Tanks,
Tanks[Day_Tank],
Tanks[INDEX]
),
RunData[fk_IBX_ID] = IBXID,
Tanks[Day_Tank] = TRUE(),
Tanks[INDEX] = 2
)
VAR DT3Date =
CALCULATE (
MAX (
RunData[RunData_Date]
),
ALLEXCEPT (
Tanks,
Tanks[Day_Tank],
Tanks[INDEX]
),
RunData[fk_IBX_ID] = IBXID,
Tanks[Day_Tank] = TRUE(),
Tanks[INDEX] = 3
)
VAR DT4Date =
CALCULATE (
MAX (
RunData[RunData_Date]
),
ALLEXCEPT (
Tanks,
Tanks[Day_Tank],
Tanks[INDEX]
),
RunData[fk_IBX_ID] = IBXID,
Tanks[Day_Tank] = TRUE(),
Tanks[INDEX] = 4
)
VAR DT5Date =
CALCULATE (
MAX (
RunData[RunData_Date]
),
ALLEXCEPT (
Tanks,
Tanks[Day_Tank],
Tanks[INDEX]
),
RunData[fk_IBX_ID] = IBXID,
Tanks[Day_Tank] = TRUE(),
Tanks[INDEX] = 5
)
VAR DT6Date =
CALCULATE (
MAX (
RunData[RunData_Date]
),
ALLEXCEPT (
Tanks,
Tanks[Day_Tank],
Tanks[INDEX]
),
RunData[fk_IBX_ID] = IBXID,
Tanks[Day_Tank] = TRUE(),
Tanks[INDEX] = 6
)
VAR DT7Date =
CALCULATE (
MAX (
RunData[RunData_Date]
),
ALLEXCEPT (
Tanks,
Tanks[Day_Tank],
Tanks[INDEX]
),
RunData[fk_IBX_ID] = IBXID,
Tanks[Day_Tank] = TRUE(),
Tanks[INDEX] = 7
)
VAR DT8Date =
CALCULATE (
MAX (
RunData[RunData_Date]
),
ALLEXCEPT (
Tanks,
Tanks[Day_Tank],
Tanks[INDEX]
),
RunData[fk_IBX_ID] = IBXID,
Tanks[Day_Tank] = TRUE(),
Tanks[INDEX] = 8
)
RETURN
CALCULATE (
MAX ( RunData[DT_Level] ),
FILTER (
RunData,
AND (
RunData[RunData_Date] = DT1Date,
RunData[fk_IBX_ID] = IBXID
)
)
)+
CALCULATE (
MAX ( RunData[DT_Level] ),
FILTER (
RunData,
AND (
RunData[RunData_Date] = DT2Date,
RunData[fk_IBX_ID] = IBXID
)
)
)+
CALCULATE (
MAX ( RunData[DT_Level] ),
FILTER (
RunData,
AND (
RunData[RunData_Date] = DT3Date,
RunData[fk_IBX_ID] = IBXID
)
)
)+
CALCULATE (
MAX ( RunData[DT_Level] ),
FILTER (
RunData,
AND (
RunData[RunData_Date] = DT4Date,
RunData[fk_IBX_ID] = IBXID
)
)
)+
CALCULATE (
MAX ( RunData[DT_Level] ),
FILTER (
RunData,
AND (
RunData[RunData_Date] = DT5Date,
RunData[fk_IBX_ID] = IBXID
)
)
)+
CALCULATE (
MAX ( RunData[DT_Level] ),
FILTER (
RunData,
AND (
RunData[RunData_Date] = DT6Date,
RunData[fk_IBX_ID] = IBXID
)
)
)+
CALCULATE (
MAX ( RunData[DT_Level] ),
FILTER (
RunData,
AND (
RunData[RunData_Date] = DT7Date,
RunData[fk_IBX_ID] = IBXID
)
)
)If you look at the table result, the DC11 values for the two measures should be the same as the first value.
For DC6, only two of the six values have the same last date, so the highlighted value is about 1/3 of what it should be.
Please help.
1 ACCEPTED SOLUTION
Helpful resources
Power BI Monthly Update - April 2024
Check out the April 2024 Power BI update to learn about new features.
