Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
vhugozg
Frequent Visitor

Filter date by last date from another table

Hi everyone,

 

I'm trying to filter a table related to the last date from another one. These dates can't have a direct relation, so I tried to create measures, calculated columns, and much more to accomplish what I'm wanting.

So here are some images of what I'm trying to do:

vhugozg_0-1638834182449.png

The measure "Calendar Ultima Data" is just the last date from the Calendar used by the slicer above.

"Data de cadastro" should be less than "Calendar Ultima Data", which is being filtered by the slicer at the top of the image.
The calculated column next to "Calendar Ultima Data" ("CadastroMenorQue"), in this case showing as "True", has this formula:

vhugozg_1-1638834370717.png


So, if "Data de cadastro" has a value greater than "Calendar Ultima Data", it should appear as FALSE, not TRUE, but that is not what is happening. 

The last column (MaxCalendar) is a attempt to create a column to show exactly the same date as "Calendar Ultima Data" to try to relate both columns and get the result I want.

Don't know what I'm doing wrong, and would really appreciate some help here.

Thanks in advance.



5 REPLIES 5
v-xiaotang
Community Support
Community Support

Hi @vhugozg 

Hi @vhugozg 

Try this, create the measures

Calendar Ultima Data = CALCULATE(MAX('Calendar'[Date]),ALLSELECTED('Calendar'))
CadastroMenorQue = IF(MIN(InativosDatas[Data de cadastro])>[Calendar Ultima Data],FALSE(),TRUE())

 

 

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-xiaotang , thank you for the reply.
I tried exactly as you said, but stills the same behavior. I can't understand what is happening with PBI.

I also remake my table with a measure just to see how it would work.

vhugozg_0-1639057872895.png

 

vhugozg_1-1639057976391.png

It make no sense to me that the table show two dates, compare it, and shows incorrect values.



Hi @vhugozg 

Also confused about this, I haven't encountered it before, could you provide a sample file with this issue for us to test? thanks, curious about the reason for this behavior.

 

 

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

amitchandak
Super User
Super User

@vhugozg , if you want to use max date from another table in a table then try

if(Table1[Date] < Max(Table2[Date]) ,1, 0)

Hi @amitchandak , firstly thank you for the response and help.

I tried to do exactly as you said in the column "CadastroMenorQue":

vhugozg_0-1638889815521.png

But the results always returns 0.

vhugozg_1-1638889914951.png


I don't know why this is not filtering the max date. The last column I used the same max formula you sent, but it stills show as 31/12/2022.

vhugozg_2-1638890028023.png

vhugozg_3-1638890074409.png

 



Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.