Forum Discussion
Create graph that includes all dates between a start and end date
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.
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]
)
)https://analyticpulse.blogspot.com
4 Replies
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]
)
)https://analyticpulse.blogspot.com
