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. 

     

     

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hello,

Thank you both for your responses. 

 

When I tried the first approach I am getting this error

 

When I tried the second approach, after step 3 of close and apply I am getting this message and cannot move forward.  Please assist.

 

 

Thank you!

 

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi Anonymous ,

 

Please disconnect the model relationship in Power BI Desktop then go into power query to process the data then close and apply then connect your model relationship again.

 

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.