Compare Dates, Hour, Minute between two Data set and return Value

[Anonymous]

Hello,

I have two set of data as below.

My need to compare the Reference (Date, Hour, Minute) with Actual Source Data (Date, Hour, Minute) and returns the Value if Date, Hour, Minute is matched.

Basically, the operation will have to happen like below and go on:

I tried doing this with Loop and If Condition, but I couldn’t succeed. Can anyone please help or provide me a lead to make this happen!! Looking forward!! Thank you!!!

[v-juanli-msft]

Hi @Anonymous 

Is this problem sloved? 

If it is sloved, could you kindly accept it as a solution to close this case?

If not, please let me know.

Best Regards

Maggie

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

I get a result table as below ( filter [Hour] <=5 and [Date] =2019/1/1, so that it displays more clearly)

If it meets your requirement, please refer to the steps below

1. In Edit queries, in "Date" table, Add two custom column

Custom=#"Hour"

Custom.1=#"Minute"

then expand two custom columns

2.

In "Date" table,

select "Date", 'Custom.Hour", "Custom.1.Minute" , then "Add column"->Merge column(Separator is "-" in my example)

Then in "Actual Source Table", 

select "Date", 'Hour", "Minute" , then "Add column"->Merge column(Separator is "-" too)

3.

In "Date" table, merge queries, then expand "Merged 2" column from "Source table"

4. Close&&apply, create a cacluated column

count = CALCULATE(COUNTROWS('Date'),FILTER(ALLEXCEPT('Date','Date'[Date]),[Actual Source Table.Merged2]<>BLANK()))

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

Check out the function LOOKUPVALUE, that should be all you need. I do not see a need for looping here.

[Anonymous]

Thanks for the HeadsUp!!!

Actually I need to compare the Date, Hour & Minute and get the Value and count as well. Like how much value for Date 1/1/2019, 1/2/2019 and so on. It doesn't end up with 3 Dates. It may keep going for a year!! That's why i thought of Loop and If condition.

But I had an idea like below:

Output = Count of (Lookup( Reference Value, Reference Date = Source Date, Reference Hour = Source Hour, Reference Minute = Source Minute, "0", "1"))

Please let me know if the above concept works!! First it will look up the Date,Hour,Minute and then return a Value.

That Value will be counted as I am using Count function.