Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Charting a date range

Below is an example of data I have.  I have a list of assignments, when they begin and when they end.  As you can see some assignment dates overlap each other meaning they are active during the same ...
Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Anonymous ,

     

    Thanks amitchandak  for the quick reply and solution. Here is my alternative approach for your reference:

    1.Click "transform data" to enter the power query and add a custom column.

     

    List.Dates([Start Date],Duration.Days([End Date]-[Start Date])+1,#duration(1,0,0,0))

     

    2.Expand to New Rows.

    3.Changes the data type to a date type.->Close and Apply.

    4.We can create a date table.

     

    Date = CALENDAR(DATE(2023,1,1),DATE(2024,12,31))

     

    5.We can create a measure.

     

    Measure = 
var _table=ADDCOLUMNS(ALL('Table'),"Year",YEAR([Custom Date]),"Month",MONTH([Custom Date]))
var _table2=SUMMARIZE(_table,[Assignment #],[Year],[Month])
RETURN 
COUNTROWS(FILTER(_table2,[Month]=MAX('Date'[Date].[MonthNo]) && [Year] =MAX('Date'[Date].[Year])))

     

    Best Regards,

    Neeko Tang

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

     

     

amitchandak's avatar
amitchandak
Super User
2 years ago

Anonymous ,With a date table having active join with Start date and inactive join with end date

 

Started = CALCULATE(COUNT(Assignment[Assignment #]),USERELATIONSHIP(Assignment[Start Date],'Date'[Date]) )

Ended = CALCULATE(COUNT(Assignment[Assignment #]),USERELATIONSHIP(Assignment[End Date],'Date'[Date]),not(ISBLANK(Assignment[End Date])))

Active Assignment = CALCULATE(COUNTx(FILTER(Assignment,Assignment[Start Date]<=max('Date'[Date]) && (ISBLANK(Assignment[End Date]) || Assignment[End Date]>max('Date'[Date]))),(Assignment[Assignment #])),CROSSFILTER(Assignment[Start Date],'Date'[Date],None))

 

 

Seem like very similar to HR Analytic Solution

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-trend/ba-p/882970
Power BI HR Active Employee Tenure Bucketing, and Hired, Terminated, and Active employees: https://youtu.be/fvgcx8QLqZU