Forum Discussion

Bimo2023's avatar
Bimo2023
Regular Visitor
3 years ago

Column to return value from another table based on Dates

Hi 

I have one table1 with non consecutive dates and values, I have created a calendar table with consecutive date and would like to add a column on calendar date to show values from Table1

 

I have tried below formula but I get error "FILTER used in True/False..." 

CALCULATE(FIRSTNONBLANK(Readings[Electricity usage],1),
FILTER(ALL(Readings),Readings[Date]>='Calendar'[Date])
&&Readings[Previous Date]<='Calendar'[Date])

 

desired output 

 

 

Can you please assist ?

 

Thanks 

 

Need Help

2 Replies

  • AnastasiaS's avatar
    AnastasiaS
    Resolver I
    3 years ago

    Hello Bimo2023 

     

    Could you try please the following formula?

     

    output =
        CALCULATE (
            LASTNONBLANK ( Readings[Electricity usage], 1 ),
            FILTER (
                ALL (Readings ),
                Readings[Date] <= EARLIER ('Calendar'[Date] )
            )
        )

     

    Regards,

  • MohTawfik's avatar
    MohTawfik
    Resolver I
    3 years ago
    Electricity Usage = 
VAR CurrentDate = 'Calendar'[Date]
VAR PreviousDate =
    CALCULATE(
        MAX(Readings[Date]),
        Readings[Date] <= CurrentDate
    )
RETURN
    CALCULATE(
        MAX(Readings[Electricity usage]),
        Readings[Date] = PreviousDate
    )

    Try this one