How can I get a function to content a date and time as 1 in another column

[Anonymous]

Hello,

I am trying to get power BI to count my lead date and time as one.

So when lead comes in at a specific time and date it is counted as one in another column and I then total them up based on the days.

Does any one know of the best way to do this?

Kind regards,

Harrison

[amitchandak · Accepted solution]

@Anonymous , you can create dates by removing timepart 

example new column 

date = datevalue([datetime])

After you have both date columns join them with a common date table and you will have one inactive join, which you can activate using userelationship

https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in-power-bi

Calendar = Addcolumns(calendar(date(2020,01,01), date(2021,12,31) ), "Month no" , month([date])
, "Year", year([date])
, "Month Year", format([date],"mmm-yyyy")
, "Month year sort", year([date])*100 + month([date])
, "Qtr Year", format([date],"yyyy-\QQ")
, "Qtr", quarter([date])
, "Month",FORMAT([Date],"mmmm")
, "Month sort", month([DAte])
, "Is Today" ,if([Date]=TODAY(),"Today",[Date]&"")
,"Day of Year" , datediff(date(year([DAte]),1,1), [Date], day)+1
, "Month Type", Switch( True(),
eomonth([Date],0) = eomonth(Today(),-1),"Last Month" ,
eomonth([Date],0)= eomonth(Today(),0),"This Month" ,
Format([Date],"MMM-YYYY") )
,"Year Type" , Switch( True(),
year([Date])= year(Today()),"This Year" ,
year([Date])= year(Today())-1,"Last Year" ,
Format([Date],"YYYY")
)
)

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

[Anonymous]

Thank you Amitchandak for the quick response.

Would you happen to have a pbix which demonstrates your solution?

I did follow your example link it was not clear for me.