cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
HarryB
Helper I
Helper I

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

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?

 

HarryB_0-1670256211288.png

 

Kind regards,

 

Harrison

 

 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@HarryB , 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...

 

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-tr...



Learn Power BI - Full Course with Dec-2022, with new DAX functions like Window, Index, Offset !!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Formatted Profit and Loss Statement with empty lines

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@HarryB , 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...

 

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-tr...



Learn Power BI - Full Course with Dec-2022, with new DAX functions like Window, Index, Offset !!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Formatted Profit and Loss Statement with empty lines

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.

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.