Forum Discussion

tkrupka's avatar
tkrupka
Resolver 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.   LastTotalD...
  • tkrupka's avatar
    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
        )