Issue in creating Calculated column for handling blank in Date column

[Anony_mous]

Hi All,

I am new to Power BI I have a requirement like I have 2 columns from same table that is Start Date and End Date.Start Date and End Date are of text data type I need to convert it into date data type so that all months will appear in a sequential order for Example Jan 2022,Feb 2022,Mar 2022,Apr 2022,May 2022,.......,Jan 2023,Feb 2023,Mar 2023. Where I need to combine the dates in 2 column(Start Date and End Date) into 1 column by taking the distinct of 2 columns. Since I have the blank in both the columns Start Date and End Date. I am getting the error like( in table '' contains blank values and this is not allowed for columns on the one side of a many-to-one relationship or for columns that are used as the primary key of a table)

this.As of now I am trying to create a separate table for combined Start Date and End Date.Again I am getting the same error I wrote above.Since both Start Date and End Date are calculated column I cannot do any logic on this in Power Query.So I need to work using DAX .Please give some solution to handle this.All suggestions are welcome.Thank you in advance!!
Regards,

Anony_mous

[amitchandak]

@Anony_mous , you can handle blank like

Date St = if(isblank([Start Date]) || [Start Date] = "" , blank(), [Start Date])

for having months consider date/calendar table. In this case with active/inactive join or both inactive joins

Calendar = Addcolumns(calendar(date(2012,01,01), date(2024,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])
, "FY Year", if( Month(_max) <7 , year(_max)-1 ,year(_max))
, "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")
)
)

refer to how to work with two dates in the blog, video, and attached files

Power BI: HR Analytics - Employees as on Date : https://youtu.be/e6Y-l_JtCq4
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...