Forum Discussion

tkrupka's avatar
tkrupka
Icon for Resolver II rankResolver II
6 years ago
Solved

Add items with different latest dates

PBIX File

 

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.

Need Help
  • tkrupka's avatar
    tkrupka
    6 years ago

    I figured it out.

     

    But, if someone knows a better way than all of the manual iterations, I'm all ears.

     

    LastTotalDTLevel = 
	VAR IBXID =
		LOOKUPVALUE ( 
			IBXs[IBX_ID], 
			IBXs[IBX_Name], 
			SELECTEDVALUE ( IBXs[IBX_Name] ) 
		)
    VAR DT1 =
        LOOKUPVALUE (
            Tanks[fk_GeneratorName_ID],
            Tanks[INDEX],
            1,
            Tanks[fk_IBX_ID],
            IBXID,
            Tanks[Day_Tank],
            TRUE ()
        )
	VAR DT1Date =
        CALCULATE (
            LASTDATE (
                RunData[RunData_Date]
            ),
            ALLEXCEPT (
                RunData,
                RunData[fk_GeneratorName_ID]
            ),
            RunData[fk_GeneratorName_ID] = DT1
        )
    VAR Run1 =
        LOOKUPVALUE (
            RunData[RunData_ID],
            RunData[fk_IBX_ID],
            IBXID,
            RunData[fk_GeneratorName_ID],
            DT1,
            RunData[RunData_Date],
            DT1Date
        )
    VAR DT2 =
        LOOKUPVALUE (
            Tanks[fk_GeneratorName_ID],
            Tanks[INDEX],
            2,
            Tanks[fk_IBX_ID],
            IBXID,
            Tanks[Day_Tank],
            TRUE ()
        )
	VAR DT2Date =
        CALCULATE (
            LASTDATE (
                RunData[RunData_Date]
            ),
            ALLEXCEPT (
                RunData,
                RunData[fk_GeneratorName_ID]
            ),
            RunData[fk_GeneratorName_ID] = DT2
        )
    VAR Run2 =
        LOOKUPVALUE (
            RunData[RunData_ID],
            RunData[fk_IBX_ID],
            IBXID,
            RunData[fk_GeneratorName_ID],
            DT2,
            RunData[RunData_Date],
            DT2Date
        )
    VAR DT3 =
        LOOKUPVALUE (
            Tanks[fk_GeneratorName_ID],
            Tanks[INDEX],
            3,
            Tanks[fk_IBX_ID],
            IBXID,
            Tanks[Day_Tank],
            TRUE ()
        )
	VAR DT3Date =
        CALCULATE (
            LASTDATE (
                RunData[RunData_Date]
            ),
            ALLEXCEPT (
                RunData,
                RunData[fk_GeneratorName_ID]
            ),
            RunData[fk_GeneratorName_ID] = DT3
        )
    VAR Run3 =
        LOOKUPVALUE (
            RunData[RunData_ID],
            RunData[fk_IBX_ID],
            IBXID,
            RunData[fk_GeneratorName_ID],
            DT3,
            RunData[RunData_Date],
            DT3Date
        )
    VAR DT4 =
        LOOKUPVALUE (
            Tanks[fk_GeneratorName_ID],
            Tanks[INDEX],
            4,
            Tanks[fk_IBX_ID],
            IBXID,
            Tanks[Day_Tank],
            TRUE ()
        )
	VAR DT4Date =
        CALCULATE (
            LASTDATE (
                RunData[RunData_Date]
            ),
            ALLEXCEPT (
                RunData,
                RunData[fk_GeneratorName_ID]
            ),
            RunData[fk_GeneratorName_ID] = DT4
        )
    VAR Run4 =
        LOOKUPVALUE (
            RunData[RunData_ID],
            RunData[fk_IBX_ID],
            IBXID,
            RunData[fk_GeneratorName_ID],
            DT4,
            RunData[RunData_Date],
            DT4Date
        )
    VAR DT5 =
        LOOKUPVALUE (
            Tanks[fk_GeneratorName_ID],
            Tanks[INDEX],
            5,
            Tanks[fk_IBX_ID],
            IBXID,
            Tanks[Day_Tank],
            TRUE ()
        )
	VAR DT5Date =
        CALCULATE (
            LASTDATE (
                RunData[RunData_Date]
            ),
            ALLEXCEPT (
                RunData,
                RunData[fk_GeneratorName_ID]
            ),
            RunData[fk_GeneratorName_ID] = DT5
        )
    VAR Run5 =
        LOOKUPVALUE (
            RunData[RunData_ID],
            RunData[fk_IBX_ID],
            IBXID,
            RunData[fk_GeneratorName_ID],
            DT5,
            RunData[RunData_Date],
            DT5Date
        )
    VAR DT6 =
        LOOKUPVALUE (
            Tanks[fk_GeneratorName_ID],
            Tanks[INDEX],
            6,
            Tanks[fk_IBX_ID],
            IBXID,
            Tanks[Day_Tank],
            TRUE ()
        )
	VAR DT6Date =
        CALCULATE (
            LASTDATE (
                RunData[RunData_Date]
            ),
            ALLEXCEPT (
                RunData,
                RunData[fk_GeneratorName_ID]
            ),
            RunData[fk_GeneratorName_ID] = DT6
        )
    VAR Run6 =
        LOOKUPVALUE (
            RunData[RunData_ID],
            RunData[fk_IBX_ID],
            IBXID,
            RunData[fk_GeneratorName_ID],
            DT6,
            RunData[RunData_Date],
            DT6Date
        )
    VAR DT7 =
        LOOKUPVALUE (
            Tanks[fk_GeneratorName_ID],
            Tanks[INDEX],
            7,
            Tanks[fk_IBX_ID],
            IBXID,
            Tanks[Day_Tank],
            TRUE ()
        )
	VAR DT7Date =
        CALCULATE (
            LASTDATE (
                RunData[RunData_Date]
            ),
            ALLEXCEPT (
                RunData,
                RunData[fk_GeneratorName_ID]
            ),
            RunData[fk_GeneratorName_ID] = DT7
        )
    VAR Run7 =
        LOOKUPVALUE (
            RunData[RunData_ID],
            RunData[fk_IBX_ID],
            IBXID,
            RunData[fk_GeneratorName_ID],
            DT7,
            RunData[RunData_Date],
            DT7Date
        )
    VAR DT8 =
        LOOKUPVALUE (
            Tanks[fk_GeneratorName_ID],
            Tanks[INDEX],
            8,
            Tanks[fk_IBX_ID],
            IBXID,
            Tanks[Day_Tank],
            TRUE ()
        )
	VAR DT8Date =
        CALCULATE (
            LASTDATE (
                RunData[RunData_Date]
            ),
            ALLEXCEPT (
                RunData,
                RunData[fk_GeneratorName_ID]
            ),
            RunData[fk_GeneratorName_ID] = DT8
        )
    VAR Run8 =
        LOOKUPVALUE (
            RunData[RunData_ID],
            RunData[fk_IBX_ID],
            IBXID,
            RunData[fk_GeneratorName_ID],
            DT8,
            RunData[RunData_Date],
            DT8Date
        )
    VAR DT9 =
        LOOKUPVALUE (
            Tanks[fk_GeneratorName_ID],
            Tanks[INDEX],
            9,
            Tanks[fk_IBX_ID],
            IBXID,
            Tanks[Day_Tank],
            TRUE ()
        )
	VAR DT9Date =
        CALCULATE (
            LASTDATE (
                RunData[RunData_Date]
            ),
            ALLEXCEPT (
                RunData,
                RunData[fk_GeneratorName_ID]
            ),
            RunData[fk_GeneratorName_ID] = DT9
        )
    VAR Run9 =
        LOOKUPVALUE (
            RunData[RunData_ID],
            RunData[fk_IBX_ID],
            IBXID,
            RunData[fk_GeneratorName_ID],
            DT9,
            RunData[RunData_Date],
            DT9Date
        )
    VAR DT10 =
        LOOKUPVALUE (
            Tanks[fk_GeneratorName_ID],
            Tanks[INDEX],
            10,
            Tanks[fk_IBX_ID],
            IBXID,
            Tanks[Day_Tank],
            TRUE ()
        )
	VAR DT10Date =
        CALCULATE (
            LASTDATE (
                RunData[RunData_Date]
            ),
            ALLEXCEPT (
                RunData,
                RunData[fk_GeneratorName_ID]
            ),
            RunData[fk_GeneratorName_ID] = DT10
        )
    VAR Run10 =
        LOOKUPVALUE (
            RunData[RunData_ID],
            RunData[fk_IBX_ID],
            IBXID,
            RunData[fk_GeneratorName_ID],
            DT10,
            RunData[RunData_Date],
            DT10Date
        )
    VAR DT11 =
        LOOKUPVALUE (
            Tanks[fk_GeneratorName_ID],
            Tanks[INDEX],
            11,
            Tanks[fk_IBX_ID],
            IBXID,
            Tanks[Day_Tank],
            TRUE ()
        )
	VAR DT11Date =
        CALCULATE (
            LASTDATE (
                RunData[RunData_Date]
            ),
            ALLEXCEPT (
                RunData,
                RunData[fk_GeneratorName_ID]
            ),
            RunData[fk_GeneratorName_ID] = DT11
        )
    VAR Run11 =
        LOOKUPVALUE (
            RunData[RunData_ID],
            RunData[fk_IBX_ID],
            IBXID,
            RunData[fk_GeneratorName_ID],
            DT11,
            RunData[RunData_Date],
            DT11Date
        )
    VAR DT12 =
        LOOKUPVALUE (
            Tanks[fk_GeneratorName_ID],
            Tanks[INDEX],
            12,
            Tanks[fk_IBX_ID],
            IBXID,
            Tanks[Day_Tank],
            TRUE ()
        )
	VAR DT12Date =
        CALCULATE (
            LASTDATE (
                RunData[RunData_Date]
            ),
            ALLEXCEPT (
                RunData,
                RunData[fk_GeneratorName_ID]
            ),
            RunData[fk_GeneratorName_ID] = DT12
        )
    VAR Run12 =
        LOOKUPVALUE (
            RunData[RunData_ID],
            RunData[fk_IBX_ID],
            IBXID,
            RunData[fk_GeneratorName_ID],
            DT12,
            RunData[RunData_Date],
            DT12Date
        )
    VAR DT13 =
        LOOKUPVALUE (
            Tanks[fk_GeneratorName_ID],
            Tanks[INDEX],
            13,
            Tanks[fk_IBX_ID],
            IBXID,
            Tanks[Day_Tank],
            TRUE ()
        )
	VAR DT13Date =
        CALCULATE (
            LASTDATE (
                RunData[RunData_Date]
            ),
            ALLEXCEPT (
                RunData,
                RunData[fk_GeneratorName_ID]
            ),
            RunData[fk_GeneratorName_ID] = DT13
        )
    VAR Run13 =
        LOOKUPVALUE (
            RunData[RunData_ID],
            RunData[fk_IBX_ID],
            IBXID,
            RunData[fk_GeneratorName_ID],
            DT13,
            RunData[RunData_Date],
            DT13Date
        )
    VAR DT14 =
        LOOKUPVALUE (
            Tanks[fk_GeneratorName_ID],
            Tanks[INDEX],
            14,
            Tanks[fk_IBX_ID],
            IBXID,
            Tanks[Day_Tank],
            TRUE ()
        )
	VAR DT14Date =
        CALCULATE (
            LASTDATE (
                RunData[RunData_Date]
            ),
            ALLEXCEPT (
                RunData,
                RunData[fk_GeneratorName_ID]
            ),
            RunData[fk_GeneratorName_ID] = DT14
        )
    VAR Run14 =
        LOOKUPVALUE (
            RunData[RunData_ID],
            RunData[fk_IBX_ID],
            IBXID,
            RunData[fk_GeneratorName_ID],
            DT14,
            RunData[RunData_Date],
            DT14Date
        )
    VAR DT15 =
        LOOKUPVALUE (
            Tanks[fk_GeneratorName_ID],
            Tanks[INDEX],
            15,
            Tanks[fk_IBX_ID],
            IBXID,
            Tanks[Day_Tank],
            TRUE ()
        )
	VAR DT15Date =
        CALCULATE (
            LASTDATE (
                RunData[RunData_Date]
            ),
            ALLEXCEPT (
                RunData,
                RunData[fk_GeneratorName_ID]
            ),
            RunData[fk_GeneratorName_ID] = DT15
        )
    VAR Run15 =
        LOOKUPVALUE (
            RunData[RunData_ID],
            RunData[fk_IBX_ID],
            IBXID,
            RunData[fk_GeneratorName_ID],
            DT15,
            RunData[RunData_Date],
            DT15Date
        )
    VAR DT16 =
        LOOKUPVALUE (
            Tanks[fk_GeneratorName_ID],
            Tanks[INDEX],
            16,
            Tanks[fk_IBX_ID],
            IBXID,
            Tanks[Day_Tank],
            TRUE ()
        )
	VAR DT16Date =
        CALCULATE (
            LASTDATE (
                RunData[RunData_Date]
            ),
            ALLEXCEPT (
                RunData,
                RunData[fk_GeneratorName_ID]
            ),
            RunData[fk_GeneratorName_ID] = DT16
        )
    VAR Run16 =
        LOOKUPVALUE (
            RunData[RunData_ID],
            RunData[fk_IBX_ID],
            IBXID,
            RunData[fk_GeneratorName_ID],
            DT16,
            RunData[RunData_Date],
            DT16Date
        )
    VAR DT17 =
        LOOKUPVALUE (
            Tanks[fk_GeneratorName_ID],
            Tanks[INDEX],
            17,
            Tanks[fk_IBX_ID],
            IBXID,
            Tanks[Day_Tank],
            TRUE ()
        )
	VAR DT17Date =
        CALCULATE (
            LASTDATE (
                RunData[RunData_Date]
            ),
            ALLEXCEPT (
                RunData,
                RunData[fk_GeneratorName_ID]
            ),
            RunData[fk_GeneratorName_ID] = DT17
        )
    VAR Run17 =
        LOOKUPVALUE (
            RunData[RunData_ID],
            RunData[fk_IBX_ID],
            IBXID,
            RunData[fk_GeneratorName_ID],
            DT17,
            RunData[RunData_Date],
            DT17Date
        )
    VAR DT18 =
        LOOKUPVALUE (
            Tanks[fk_GeneratorName_ID],
            Tanks[INDEX],
            18,
            Tanks[fk_IBX_ID],
            IBXID,
            Tanks[Day_Tank],
            TRUE ()
        )
	VAR DT18Date =
        CALCULATE (
            LASTDATE (
                RunData[RunData_Date]
            ),
            ALLEXCEPT (
                RunData,
                RunData[fk_GeneratorName_ID]
            ),
            RunData[fk_GeneratorName_ID] = DT18
        )
    VAR Run18 =
        LOOKUPVALUE (
            RunData[RunData_ID],
            RunData[fk_IBX_ID],
            IBXID,
            RunData[fk_GeneratorName_ID],
            DT18,
            RunData[RunData_Date],
            DT18Date
        )
    VAR DT19 =
        LOOKUPVALUE (
            Tanks[fk_GeneratorName_ID],
            Tanks[INDEX],
            19,
            Tanks[fk_IBX_ID],
            IBXID,
            Tanks[Day_Tank],
            TRUE ()
        )
	VAR DT19Date =
        CALCULATE (
            LASTDATE (
                RunData[RunData_Date]
            ),
            ALLEXCEPT (
                RunData,
                RunData[fk_GeneratorName_ID]
            ),
            RunData[fk_GeneratorName_ID] = DT19
        )
    VAR Run19 =
        LOOKUPVALUE (
            RunData[RunData_ID],
            RunData[fk_IBX_ID],
            IBXID,
            RunData[fk_GeneratorName_ID],
            DT19,
            RunData[RunData_Date],
            DT19Date
        )
    VAR DT20 =
        LOOKUPVALUE (
            Tanks[fk_GeneratorName_ID],
            Tanks[INDEX],
            20,
            Tanks[fk_IBX_ID],
            IBXID,
            Tanks[Day_Tank],
            TRUE ()
        )
	VAR DT20Date =
        CALCULATE (
            LASTDATE (
                RunData[RunData_Date]
            ),
            ALLEXCEPT (
                RunData,
                RunData[fk_GeneratorName_ID]
            ),
            RunData[fk_GeneratorName_ID] = DT20
        )
    VAR Run20 =
        LOOKUPVALUE (
            RunData[RunData_ID],
            RunData[fk_IBX_ID],
            IBXID,
            RunData[fk_GeneratorName_ID],
            DT20,
            RunData[RunData_Date],
            DT20Date
        )
RETURN

	LOOKUPVALUE ( 
        RunData[DT_Level],
        RunData[RunData_ID],
        Run1
    )+
    LOOKUPVALUE ( 
        RunData[DT_Level],
        RunData[RunData_ID],
        Run2
    )+
    LOOKUPVALUE ( 
        RunData[DT_Level],
        RunData[RunData_ID],
        Run3
    )+
    LOOKUPVALUE ( 
        RunData[DT_Level],
        RunData[RunData_ID],
        Run4
    )+
    LOOKUPVALUE ( 
        RunData[DT_Level],
        RunData[RunData_ID],
        Run5
    )+
    LOOKUPVALUE ( 
        RunData[DT_Level],
        RunData[RunData_ID],
        Run6
    )+
    LOOKUPVALUE ( 
        RunData[DT_Level],
        RunData[RunData_ID],
        Run7
    )+
    LOOKUPVALUE ( 
        RunData[DT_Level],
        RunData[RunData_ID],
        Run8
    )+
    LOOKUPVALUE ( 
        RunData[DT_Level],
        RunData[RunData_ID],
        Run9
    )+
    LOOKUPVALUE ( 
        RunData[DT_Level],
        RunData[RunData_ID],
        Run10
    )+LOOKUPVALUE ( 
        RunData[DT_Level],
        RunData[RunData_ID],
        Run11
    )+
    LOOKUPVALUE ( 
        RunData[DT_Level],
        RunData[RunData_ID],
        Run12
    )+
    LOOKUPVALUE ( 
        RunData[DT_Level],
        RunData[RunData_ID],
        Run13
    )+
    LOOKUPVALUE ( 
        RunData[DT_Level],
        RunData[RunData_ID],
        Run14
    )+
    LOOKUPVALUE ( 
        RunData[DT_Level],
        RunData[RunData_ID],
        Run15
    )+
    LOOKUPVALUE ( 
        RunData[DT_Level],
        RunData[RunData_ID],
        Run16
    )+
    LOOKUPVALUE ( 
        RunData[DT_Level],
        RunData[RunData_ID],
        Run17
    )+
    LOOKUPVALUE ( 
        RunData[DT_Level],
        RunData[RunData_ID],
        Run18
    )+
    LOOKUPVALUE ( 
        RunData[DT_Level],
        RunData[RunData_ID],
        Run19
    )+
    LOOKUPVALUE ( 
        RunData[DT_Level],
        RunData[RunData_ID],
        Run20
    )


     

1 Reply

  • tkrupka's avatar
    tkrupka
    Icon for Resolver II rankResolver II
    6 years ago

    I figured it out.

     

    But, if someone knows a better way than all of the manual iterations, I'm all ears.

     

    LastTotalDTLevel = 
	VAR IBXID =
		LOOKUPVALUE ( 
			IBXs[IBX_ID], 
			IBXs[IBX_Name], 
			SELECTEDVALUE ( IBXs[IBX_Name] ) 
		)
    VAR DT1 =
        LOOKUPVALUE (
            Tanks[fk_GeneratorName_ID],
            Tanks[INDEX],
            1,
            Tanks[fk_IBX_ID],
            IBXID,
            Tanks[Day_Tank],
            TRUE ()
        )
	VAR DT1Date =
        CALCULATE (
            LASTDATE (
                RunData[RunData_Date]
            ),
            ALLEXCEPT (
                RunData,
                RunData[fk_GeneratorName_ID]
            ),
            RunData[fk_GeneratorName_ID] = DT1
        )
    VAR Run1 =
        LOOKUPVALUE (
            RunData[RunData_ID],
            RunData[fk_IBX_ID],
            IBXID,
            RunData[fk_GeneratorName_ID],
            DT1,
            RunData[RunData_Date],
            DT1Date
        )
    VAR DT2 =
        LOOKUPVALUE (
            Tanks[fk_GeneratorName_ID],
            Tanks[INDEX],
            2,
            Tanks[fk_IBX_ID],
            IBXID,
            Tanks[Day_Tank],
            TRUE ()
        )
	VAR DT2Date =
        CALCULATE (
            LASTDATE (
                RunData[RunData_Date]
            ),
            ALLEXCEPT (
                RunData,
                RunData[fk_GeneratorName_ID]
            ),
            RunData[fk_GeneratorName_ID] = DT2
        )
    VAR Run2 =
        LOOKUPVALUE (
            RunData[RunData_ID],
            RunData[fk_IBX_ID],
            IBXID,
            RunData[fk_GeneratorName_ID],
            DT2,
            RunData[RunData_Date],
            DT2Date
        )
    VAR DT3 =
        LOOKUPVALUE (
            Tanks[fk_GeneratorName_ID],
            Tanks[INDEX],
            3,
            Tanks[fk_IBX_ID],
            IBXID,
            Tanks[Day_Tank],
            TRUE ()
        )
	VAR DT3Date =
        CALCULATE (
            LASTDATE (
                RunData[RunData_Date]
            ),
            ALLEXCEPT (
                RunData,
                RunData[fk_GeneratorName_ID]
            ),
            RunData[fk_GeneratorName_ID] = DT3
        )
    VAR Run3 =
        LOOKUPVALUE (
            RunData[RunData_ID],
            RunData[fk_IBX_ID],
            IBXID,
            RunData[fk_GeneratorName_ID],
            DT3,
            RunData[RunData_Date],
            DT3Date
        )
    VAR DT4 =
        LOOKUPVALUE (
            Tanks[fk_GeneratorName_ID],
            Tanks[INDEX],
            4,
            Tanks[fk_IBX_ID],
            IBXID,
            Tanks[Day_Tank],
            TRUE ()
        )
	VAR DT4Date =
        CALCULATE (
            LASTDATE (
                RunData[RunData_Date]
            ),
            ALLEXCEPT (
                RunData,
                RunData[fk_GeneratorName_ID]
            ),
            RunData[fk_GeneratorName_ID] = DT4
        )
    VAR Run4 =
        LOOKUPVALUE (
            RunData[RunData_ID],
            RunData[fk_IBX_ID],
            IBXID,
            RunData[fk_GeneratorName_ID],
            DT4,
            RunData[RunData_Date],
            DT4Date
        )
    VAR DT5 =
        LOOKUPVALUE (
            Tanks[fk_GeneratorName_ID],
            Tanks[INDEX],
            5,
            Tanks[fk_IBX_ID],
            IBXID,
            Tanks[Day_Tank],
            TRUE ()
        )
	VAR DT5Date =
        CALCULATE (
            LASTDATE (
                RunData[RunData_Date]
            ),
            ALLEXCEPT (
                RunData,
                RunData[fk_GeneratorName_ID]
            ),
            RunData[fk_GeneratorName_ID] = DT5
        )
    VAR Run5 =
        LOOKUPVALUE (
            RunData[RunData_ID],
            RunData[fk_IBX_ID],
            IBXID,
            RunData[fk_GeneratorName_ID],
            DT5,
            RunData[RunData_Date],
            DT5Date
        )
    VAR DT6 =
        LOOKUPVALUE (
            Tanks[fk_GeneratorName_ID],
            Tanks[INDEX],
            6,
            Tanks[fk_IBX_ID],
            IBXID,
            Tanks[Day_Tank],
            TRUE ()
        )
	VAR DT6Date =
        CALCULATE (
            LASTDATE (
                RunData[RunData_Date]
            ),
            ALLEXCEPT (
                RunData,
                RunData[fk_GeneratorName_ID]
            ),
            RunData[fk_GeneratorName_ID] = DT6
        )
    VAR Run6 =
        LOOKUPVALUE (
            RunData[RunData_ID],
            RunData[fk_IBX_ID],
            IBXID,
            RunData[fk_GeneratorName_ID],
            DT6,
            RunData[RunData_Date],
            DT6Date
        )
    VAR DT7 =
        LOOKUPVALUE (
            Tanks[fk_GeneratorName_ID],
            Tanks[INDEX],
            7,
            Tanks[fk_IBX_ID],
            IBXID,
            Tanks[Day_Tank],
            TRUE ()
        )
	VAR DT7Date =
        CALCULATE (
            LASTDATE (
                RunData[RunData_Date]
            ),
            ALLEXCEPT (
                RunData,
                RunData[fk_GeneratorName_ID]
            ),
            RunData[fk_GeneratorName_ID] = DT7
        )
    VAR Run7 =
        LOOKUPVALUE (
            RunData[RunData_ID],
            RunData[fk_IBX_ID],
            IBXID,
            RunData[fk_GeneratorName_ID],
            DT7,
            RunData[RunData_Date],
            DT7Date
        )
    VAR DT8 =
        LOOKUPVALUE (
            Tanks[fk_GeneratorName_ID],
            Tanks[INDEX],
            8,
            Tanks[fk_IBX_ID],
            IBXID,
            Tanks[Day_Tank],
            TRUE ()
        )
	VAR DT8Date =
        CALCULATE (
            LASTDATE (
                RunData[RunData_Date]
            ),
            ALLEXCEPT (
                RunData,
                RunData[fk_GeneratorName_ID]
            ),
            RunData[fk_GeneratorName_ID] = DT8
        )
    VAR Run8 =
        LOOKUPVALUE (
            RunData[RunData_ID],
            RunData[fk_IBX_ID],
            IBXID,
            RunData[fk_GeneratorName_ID],
            DT8,
            RunData[RunData_Date],
            DT8Date
        )
    VAR DT9 =
        LOOKUPVALUE (
            Tanks[fk_GeneratorName_ID],
            Tanks[INDEX],
            9,
            Tanks[fk_IBX_ID],
            IBXID,
            Tanks[Day_Tank],
            TRUE ()
        )
	VAR DT9Date =
        CALCULATE (
            LASTDATE (
                RunData[RunData_Date]
            ),
            ALLEXCEPT (
                RunData,
                RunData[fk_GeneratorName_ID]
            ),
            RunData[fk_GeneratorName_ID] = DT9
        )
    VAR Run9 =
        LOOKUPVALUE (
            RunData[RunData_ID],
            RunData[fk_IBX_ID],
            IBXID,
            RunData[fk_GeneratorName_ID],
            DT9,
            RunData[RunData_Date],
            DT9Date
        )
    VAR DT10 =
        LOOKUPVALUE (
            Tanks[fk_GeneratorName_ID],
            Tanks[INDEX],
            10,
            Tanks[fk_IBX_ID],
            IBXID,
            Tanks[Day_Tank],
            TRUE ()
        )
	VAR DT10Date =
        CALCULATE (
            LASTDATE (
                RunData[RunData_Date]
            ),
            ALLEXCEPT (
                RunData,
                RunData[fk_GeneratorName_ID]
            ),
            RunData[fk_GeneratorName_ID] = DT10
        )
    VAR Run10 =
        LOOKUPVALUE (
            RunData[RunData_ID],
            RunData[fk_IBX_ID],
            IBXID,
            RunData[fk_GeneratorName_ID],
            DT10,
            RunData[RunData_Date],
            DT10Date
        )
    VAR DT11 =
        LOOKUPVALUE (
            Tanks[fk_GeneratorName_ID],
            Tanks[INDEX],
            11,
            Tanks[fk_IBX_ID],
            IBXID,
            Tanks[Day_Tank],
            TRUE ()
        )
	VAR DT11Date =
        CALCULATE (
            LASTDATE (
                RunData[RunData_Date]
            ),
            ALLEXCEPT (
                RunData,
                RunData[fk_GeneratorName_ID]
            ),
            RunData[fk_GeneratorName_ID] = DT11
        )
    VAR Run11 =
        LOOKUPVALUE (
            RunData[RunData_ID],
            RunData[fk_IBX_ID],
            IBXID,
            RunData[fk_GeneratorName_ID],
            DT11,
            RunData[RunData_Date],
            DT11Date
        )
    VAR DT12 =
        LOOKUPVALUE (
            Tanks[fk_GeneratorName_ID],
            Tanks[INDEX],
            12,
            Tanks[fk_IBX_ID],
            IBXID,
            Tanks[Day_Tank],
            TRUE ()
        )
	VAR DT12Date =
        CALCULATE (
            LASTDATE (
                RunData[RunData_Date]
            ),
            ALLEXCEPT (
                RunData,
                RunData[fk_GeneratorName_ID]
            ),
            RunData[fk_GeneratorName_ID] = DT12
        )
    VAR Run12 =
        LOOKUPVALUE (
            RunData[RunData_ID],
            RunData[fk_IBX_ID],
            IBXID,
            RunData[fk_GeneratorName_ID],
            DT12,
            RunData[RunData_Date],
            DT12Date
        )
    VAR DT13 =
        LOOKUPVALUE (
            Tanks[fk_GeneratorName_ID],
            Tanks[INDEX],
            13,
            Tanks[fk_IBX_ID],
            IBXID,
            Tanks[Day_Tank],
            TRUE ()
        )
	VAR DT13Date =
        CALCULATE (
            LASTDATE (
                RunData[RunData_Date]
            ),
            ALLEXCEPT (
                RunData,
                RunData[fk_GeneratorName_ID]
            ),
            RunData[fk_GeneratorName_ID] = DT13
        )
    VAR Run13 =
        LOOKUPVALUE (
            RunData[RunData_ID],
            RunData[fk_IBX_ID],
            IBXID,
            RunData[fk_GeneratorName_ID],
            DT13,
            RunData[RunData_Date],
            DT13Date
        )
    VAR DT14 =
        LOOKUPVALUE (
            Tanks[fk_GeneratorName_ID],
            Tanks[INDEX],
            14,
            Tanks[fk_IBX_ID],
            IBXID,
            Tanks[Day_Tank],
            TRUE ()
        )
	VAR DT14Date =
        CALCULATE (
            LASTDATE (
                RunData[RunData_Date]
            ),
            ALLEXCEPT (
                RunData,
                RunData[fk_GeneratorName_ID]
            ),
            RunData[fk_GeneratorName_ID] = DT14
        )
    VAR Run14 =
        LOOKUPVALUE (
            RunData[RunData_ID],
            RunData[fk_IBX_ID],
            IBXID,
            RunData[fk_GeneratorName_ID],
            DT14,
            RunData[RunData_Date],
            DT14Date
        )
    VAR DT15 =
        LOOKUPVALUE (
            Tanks[fk_GeneratorName_ID],
            Tanks[INDEX],
            15,
            Tanks[fk_IBX_ID],
            IBXID,
            Tanks[Day_Tank],
            TRUE ()
        )
	VAR DT15Date =
        CALCULATE (
            LASTDATE (
                RunData[RunData_Date]
            ),
            ALLEXCEPT (
                RunData,
                RunData[fk_GeneratorName_ID]
            ),
            RunData[fk_GeneratorName_ID] = DT15
        )
    VAR Run15 =
        LOOKUPVALUE (
            RunData[RunData_ID],
            RunData[fk_IBX_ID],
            IBXID,
            RunData[fk_GeneratorName_ID],
            DT15,
            RunData[RunData_Date],
            DT15Date
        )
    VAR DT16 =
        LOOKUPVALUE (
            Tanks[fk_GeneratorName_ID],
            Tanks[INDEX],
            16,
            Tanks[fk_IBX_ID],
            IBXID,
            Tanks[Day_Tank],
            TRUE ()
        )
	VAR DT16Date =
        CALCULATE (
            LASTDATE (
                RunData[RunData_Date]
            ),
            ALLEXCEPT (
                RunData,
                RunData[fk_GeneratorName_ID]
            ),
            RunData[fk_GeneratorName_ID] = DT16
        )
    VAR Run16 =
        LOOKUPVALUE (
            RunData[RunData_ID],
            RunData[fk_IBX_ID],
            IBXID,
            RunData[fk_GeneratorName_ID],
            DT16,
            RunData[RunData_Date],
            DT16Date
        )
    VAR DT17 =
        LOOKUPVALUE (
            Tanks[fk_GeneratorName_ID],
            Tanks[INDEX],
            17,
            Tanks[fk_IBX_ID],
            IBXID,
            Tanks[Day_Tank],
            TRUE ()
        )
	VAR DT17Date =
        CALCULATE (
            LASTDATE (
                RunData[RunData_Date]
            ),
            ALLEXCEPT (
                RunData,
                RunData[fk_GeneratorName_ID]
            ),
            RunData[fk_GeneratorName_ID] = DT17
        )
    VAR Run17 =
        LOOKUPVALUE (
            RunData[RunData_ID],
            RunData[fk_IBX_ID],
            IBXID,
            RunData[fk_GeneratorName_ID],
            DT17,
            RunData[RunData_Date],
            DT17Date
        )
    VAR DT18 =
        LOOKUPVALUE (
            Tanks[fk_GeneratorName_ID],
            Tanks[INDEX],
            18,
            Tanks[fk_IBX_ID],
            IBXID,
            Tanks[Day_Tank],
            TRUE ()
        )
	VAR DT18Date =
        CALCULATE (
            LASTDATE (
                RunData[RunData_Date]
            ),
            ALLEXCEPT (
                RunData,
                RunData[fk_GeneratorName_ID]
            ),
            RunData[fk_GeneratorName_ID] = DT18
        )
    VAR Run18 =
        LOOKUPVALUE (
            RunData[RunData_ID],
            RunData[fk_IBX_ID],
            IBXID,
            RunData[fk_GeneratorName_ID],
            DT18,
            RunData[RunData_Date],
            DT18Date
        )
    VAR DT19 =
        LOOKUPVALUE (
            Tanks[fk_GeneratorName_ID],
            Tanks[INDEX],
            19,
            Tanks[fk_IBX_ID],
            IBXID,
            Tanks[Day_Tank],
            TRUE ()
        )
	VAR DT19Date =
        CALCULATE (
            LASTDATE (
                RunData[RunData_Date]
            ),
            ALLEXCEPT (
                RunData,
                RunData[fk_GeneratorName_ID]
            ),
            RunData[fk_GeneratorName_ID] = DT19
        )
    VAR Run19 =
        LOOKUPVALUE (
            RunData[RunData_ID],
            RunData[fk_IBX_ID],
            IBXID,
            RunData[fk_GeneratorName_ID],
            DT19,
            RunData[RunData_Date],
            DT19Date
        )
    VAR DT20 =
        LOOKUPVALUE (
            Tanks[fk_GeneratorName_ID],
            Tanks[INDEX],
            20,
            Tanks[fk_IBX_ID],
            IBXID,
            Tanks[Day_Tank],
            TRUE ()
        )
	VAR DT20Date =
        CALCULATE (
            LASTDATE (
                RunData[RunData_Date]
            ),
            ALLEXCEPT (
                RunData,
                RunData[fk_GeneratorName_ID]
            ),
            RunData[fk_GeneratorName_ID] = DT20
        )
    VAR Run20 =
        LOOKUPVALUE (
            RunData[RunData_ID],
            RunData[fk_IBX_ID],
            IBXID,
            RunData[fk_GeneratorName_ID],
            DT20,
            RunData[RunData_Date],
            DT20Date
        )
RETURN

	LOOKUPVALUE ( 
        RunData[DT_Level],
        RunData[RunData_ID],
        Run1
    )+
    LOOKUPVALUE ( 
        RunData[DT_Level],
        RunData[RunData_ID],
        Run2
    )+
    LOOKUPVALUE ( 
        RunData[DT_Level],
        RunData[RunData_ID],
        Run3
    )+
    LOOKUPVALUE ( 
        RunData[DT_Level],
        RunData[RunData_ID],
        Run4
    )+
    LOOKUPVALUE ( 
        RunData[DT_Level],
        RunData[RunData_ID],
        Run5
    )+
    LOOKUPVALUE ( 
        RunData[DT_Level],
        RunData[RunData_ID],
        Run6
    )+
    LOOKUPVALUE ( 
        RunData[DT_Level],
        RunData[RunData_ID],
        Run7
    )+
    LOOKUPVALUE ( 
        RunData[DT_Level],
        RunData[RunData_ID],
        Run8
    )+
    LOOKUPVALUE ( 
        RunData[DT_Level],
        RunData[RunData_ID],
        Run9
    )+
    LOOKUPVALUE ( 
        RunData[DT_Level],
        RunData[RunData_ID],
        Run10
    )+LOOKUPVALUE ( 
        RunData[DT_Level],
        RunData[RunData_ID],
        Run11
    )+
    LOOKUPVALUE ( 
        RunData[DT_Level],
        RunData[RunData_ID],
        Run12
    )+
    LOOKUPVALUE ( 
        RunData[DT_Level],
        RunData[RunData_ID],
        Run13
    )+
    LOOKUPVALUE ( 
        RunData[DT_Level],
        RunData[RunData_ID],
        Run14
    )+
    LOOKUPVALUE ( 
        RunData[DT_Level],
        RunData[RunData_ID],
        Run15
    )+
    LOOKUPVALUE ( 
        RunData[DT_Level],
        RunData[RunData_ID],
        Run16
    )+
    LOOKUPVALUE ( 
        RunData[DT_Level],
        RunData[RunData_ID],
        Run17
    )+
    LOOKUPVALUE ( 
        RunData[DT_Level],
        RunData[RunData_ID],
        Run18
    )+
    LOOKUPVALUE ( 
        RunData[DT_Level],
        RunData[RunData_ID],
        Run19
    )+
    LOOKUPVALUE ( 
        RunData[DT_Level],
        RunData[RunData_ID],
        Run20
    )