How to filter a list to look at TODAY and before

[BCorum]

First post here.. This forum has helped me several times in the past but I cannot find the answer to this question...

I am trying to filter a list of circuits in a table to only look at the Estimated in service date of today or before (in other words late). Below is the formula that I can make work (picked an arbitrary date) by picking all the dates that are today or before every time I run this report. However I would like to use an actual formula where I do not have to do that. TIA!

2021 EIS Circuits INCOMPLETE for 10/28/2022 12:00:00 AM =

CALCULATE(

    [2021 EIS Circuits INCOMPLETE],

    'Circuit Miles'[Max of Est. in Service Date]

        IN { "10/28/2022 12:00:00 AM" }

)

[BCorum]

The closest answer I found on here was <TODAY(), the formula looks like this

2021 EIS Circuits INCOMPLETE before TODAY =

CALCULATE(

    [2021 EIS Circuits INCOMPLETE],

    ('Circuit Miles'[Max of Est. in Service Date]<TODAY())

)

It did not work....

[Anonymous]

Hi @BCorum ,

I don't sure what do you mean "not work", is there any error or the result was not correct?

It looks like the [Max of Est. in Service Date] is text type. If you directly use it to compare with today(), it will return type error. You will need to change the column type to datetime type then the formula should work.

If the result was not correct, please show some sample data so that we could test formula for you.

Best Regards,

Jay

[BCorum]

Was not working meant that when I put that formula in the table the table goes blank. It was type text…I changed it to date and now the values show up in the table, thanks! However something is still not right… I get the exact same values in the table as ‘2021 EIS Circuits INCOMPLETE’. I can change the less than to greater than and nothing changes…

EDIT-The Fields are correct because I just wrote this (below) and it works correctly. Now I just need the correct formula to look at before today.

2021 EIS Circuits INCOMPLETE for 2021 =

CALCULATE(

    [2021 EIS Circuits INCOMPLETE],

    'Circuit Miles'[Max of Est. in Service Date].[Year] IN { 2021 }

)

[Anonymous]

Hi @BCorum ,

This formula still didn't work?

EIS Circuits INCOMPLETE before TODAY =

CALCULATE(

    [2021 EIS Circuits INCOMPLETE],

    ('Circuit Miles'[Max of Est. in Service Date]<TODAY())

)

Or you want the value in 2021 and before today?

EIS Circuits INCOMPLETE for 2021 and before today =

CALCULATE(

    [2021 EIS Circuits INCOMPLETE],

    Filter(ALLSELECTED('Circuit Miles'),'Circuit Miles'[Max of Est. in Service Date].[Year] >= 2021 && 'Circuit Miles'[Max of Est. in Service Date] < today())

)

Best Regards,

Jay

[BCorum]

Sorry that I was unclear again. 

EIS Circuits INCOMPLETE before TODAY =

CALCULATE(

    [2021 EIS Circuits INCOMPLETE],

    ('Circuit Miles'[Max of Est. in Service Date]<TODAY())

)

Does not work. It no longer gives an error but it gives me the exact same results as '2021 EIS Circuits INCOMPLETE", in other words the <TODAY() function is doing nothing. I Changed '<' to '>' to see if this changed anything and still no change.

Then to make sure everything else was working I did the simple 2021 formula and it works flawlessly, this was just a trouble shoot. I still want the formula to give me the result of BEFORE TODAY.

Thanks for the replys so far!

[Anonymous]

Hi @BCorum ,

That's weird. If you use '2020' instead, will the result change?

And how this [2021 EIS Circuits INCOMPLETE] formula looks like, [Max of Est. in Service Date] is a date type column right?

Perhaps try this formula:

CALCULATE(

    [2021 EIS Circuits INCOMPLETE],

    Filter(ALL('Circuit Miles'),'Circuit Miles'[Max of Est. in Service Date] < today())

)

If it still doesn't work, then better to share some sample data here.

Best Regards,

Jay

[BCorum]

If I use the year formula with any year it is 100% correct.

[Max of Est. in Service Date] is indeed type Date.

Below is [2021 EIS Circuits INCOMPLETE] formula. Which is also 100% correct, showing exactly what I need.

2021 EIS Circuits INCOMPLETE =

CALCULATE(

    [2021 Est in service Circuits Planned],

    ISBLANK('Circuit Miles'[Max of Circuit Complete])

)

I have titled your formula 'PBI HELP', see below.

PBI HELP = CALCULATE(

[2021 EIS Circuits INCOMPLETE],

Filter(ALL('Circuit Miles'),'Circuit Miles'[Max of Est. in Service Date] < today())

)

It kind of works, it gives me the correct total but not the correct field data, it is much closer than the previous formula you suggested.

I took a screenshot of the table I am working on. There is 1 circuit that is incomplete with an estimated in service date in 2021 in Upstate Zone. So '2021 EIS Circuits INCOMPLETE for 2021' is correct. 'PBI HELP' should look identical to '2021 EIS Circuits INCOMPLETE for 2021'.

Thanks for all of your help!

[Anonymous]

Hi @BCorum ,

CALCULATE(

    [2021 EIS Circuits INCOMPLETE],

    Filter(ALL('Circuit Miles'),'Circuit Miles'[Max of Est. in Service Date] < today()&&[Zone]=selectedvalue([Zone]))

)

It would filter the result by [Zone]. If there are any other filter conditions, you could use '&&' to add them to the formula.

Best Regards,

Jay

[BCorum]

I just want to say thank you for helping as much as you have and sorry it has taken so long. The last one is so close again!

So below is the formula I used.

PBI HELP ZONE = CALCULATE(

[2021 EIS Circuits INCOMPLETE],

Filter(ALL('Circuit Miles'),'Circuit Miles'[Max of Est. in Service Date] < today()&&[Zone]=selectedvalue('Circuit Miles'[Zone]))

)

It gives the perfect solution in the fields but now has no Total... See below