How can i compare 2 tables(2 columns from different tables) using Date and/or Hour?

[Anonymous]

Table 1 give me one range of Hour/Date.

Table 2, give me the Hour/Date plus Velocity.

I have to compare this two tables to just consider data inside the range (from Table 1) according to date and/or hour.

And the result need to give me the Date/Hour/Velocity about that data.

[Anonymous]

Column 1: Initial Date/Hour; Column 2: The range starting in the initial date (Final date = Initial Date + Range)Column 1 : Date/hour in the point (I have to filter that exactly on the range of Hour/Date from Table 1)

Im new on PBI.

Sorry for my english, is not my first language.

[v-xuding-msft]

Hi @Anonymous ,

I'm not clearly about compares two tables. I create a sample that you can reference and check if is what you want.

End Date = Table2[Start Date] + TIMEVALUE(Table2[Hours])

Measure =
IF (
    MAX ( Table1[Date] ) >= MAX ( Table2[Start Date] )
        && MAX ( Table1[Date] ) <= MAX ( Table2[End Date] ),
    MAX ( Table1[Velocity] ),
    BLANK ()
)

Best Regards,

Xue Ding

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

[Anonymous]

i did it, but my column doesn't repport anything, i think it conclude all is ''false'', this way, all things is ''blanked''. 

Do u know what i did wrong?

@v-xuding-msft 

[v-xuding-msft · Accepted solution]

Hi @Anonymous ,

Sorry for late back. I find that you create a new thread and give a sample. So I modify the formula based on your pbix file. 

For your sample, I think "Tempo" and "DataInicial" are date type. And you want them to be time type. You could use the function of TIMEVALUE to get the time values.  

Start time = TIMEVALUE(tabela2[DataInicial])
End Time = tabela2[Start time] + tabela2[Horas (h)]

Time = TIMEVALUE(tabela1[Tempo])

 And then create a measure to get the Velocidade values.

Note : There is no relationships between the tables.

Measure = 
IF (
    MAX ( tabela1[Time] ) >= MAX ( tabela2[Start time] )
        && MAX ( tabela1[Time] ) <= MAX ( tabela2[End Time] ),
    MAX ( tabela1[Velocidade(km/h)] ),
    BLANK ()
)

Please reference this blog to learn more about measure and calculated column.

https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/

Best Regards,

Xue Ding

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

[Anonymous]

Thanks @v-xuding-msft for your help.

I wrote one formula that repport me and InitialDate and EndDate for each row, including the exactly Time(Tempo in my table) in a range (InitialDate and EndDate).

This is the formula:

InitialDate = CALCULATE(VALUES(tabela2[DataInicial]);FILTER(tabela2;tabela1[Tempo]>= tabela2[DataInicial] && tabela1[Tempo]<= tabela2[EndDate]))

This one repport me InitialDate(DataInicial) for each row, but simply changing tabela2[DataInicial] for tabela2[EndDate], it repport me too the EndDate for each row.

I dont tested ur way, but i think it works. Thanks for all help.

Best regards, Daniel. 

[v-xuding-msft]

Hi @Anonymous ,

Hope that your report will run smoothly.  If you have any questions, please feel free to ask us.

Best Regards,

Xue Ding

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

[amitchandak]

Can you share some sample data. If possible please share a sample pbix file after removing sensitive information.Thanks.

My Recent Blog -

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...