Create graph that includes all dates between a start and end date

[alex9999]

Hi,

I want to create a graph where my X axis has months of the year and my Y axis has count of items. The idea is to have the graph include all the months that the item has been hired, which is identified between a start and an end date.

For example, if an item has been on-hired January 2024 and off-hired March 2024, the graph will count 1 for January, February and March.

I have the idea in my head but I am struggling to execute. My data has [item], [start_date] and [end_date].

Thank you.

[ryan_mayu · Accepted solution]

@alex9999 

try to create a date table

date = CALENDAR(min('Table'[Startdate]),max('Table'[enddate]))

then create a measure

Measure = CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[Startdate]<=min('date'[Date])&&'Table'[enddate]>=max('date'[Date])))

pls see the attachment below

[AnalyticPulse · Accepted solution]

hi @alex9999 You can try below dax and let me know if it works
Items Count =
VAR MinDate = MIN('OnboardOffboardTable'[start_date])
VAR MaxDate = MAX('OnboardOffboardTable'[end_date])
VAR CalendarTable = CALENDAR(MIN(MinDate), MAX(MaxDate))
RETURN
SUMX(
CalendarTable,
CALCULATE(
COUNTROWS('OnboardOffboardTable'),
'OnboardOffboardTable'[start_date] <= CalendarTable[Date] &&
'OnboardOffboardTable'[end_date] >= CalendarTable[Date]
)
)

Learn Power BI free:

https://analyticpulse.blogspot.com

Power BI : getting started

Dax functions

powerbi Visualisation

[ryan_mayu · Accepted solution]

@alex9999 

try to create a date table

date = CALENDAR(min('Table'[Startdate]),max('Table'[enddate]))

then create a measure

Measure = CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[Startdate]<=min('date'[Date])&&'Table'[enddate]>=max('date'[Date])))

pls see the attachment below

[alex9999]

Perfect solution. I did attempt with other forum posts but this one made it very clear to me. Thank you!

[ryan_mayu]

you are welcome