Forum Discussion
Gantt Chart with Matrix Visual
Saw this cool post and decided to try it out
https://www.linkedin.com/posts/basdohmen_powerbi-design-activity-7172551962473795584-fPP_?utm_source=share&utm_medium=member_desktop
I got very close with this
Gantt =
VAR StartDate =
CALCULATE(
MIN(Batches[ManufacturingDate]),
REMOVEFILTERS('Calendar'[Date])
)
VAR EndDate =
CALCULATE(
MIN(Batches[LabelledExpiry]),
REMOVEFILTERS('Calendar'[Date])
)
VAR ProjectPeriod =
AND(
MIN('Calendar'[Date]) >= StartDate,
MIN('Calendar'[Date]) <= EndDate
)
VAR ProjectStatus =
CALCULATE(
MIN(Batches[Status]),
REMOVEFILTERS('Calendar'[Date])
)
VAR Result =
IF(
NOT(ProjectPeriod),
BLANK(),
SWITCH(
TRUE(),
ProjectStatus = "Manufactured", 1,
ProjectStatus = "Plan", 2,
ProjectStatus = "Cancelled", 3
)
)
RETURN
Result
Can anyone see anything in the dax to make the numbers repeat every day unitl the expiry date?
Thank you so much!
OKgo OK, this worked for me. PBIX is attached below signature:
Gantt = VAR StartDate = CALCULATE( MIN(Batches[Manufacturing Date]), ALLEXCEPT('Batches','Batches'[Status]) ) VAR EndDate = CALCULATE( MIN(Batches[Labelled Expiry]), ALLEXCEPT('Batches','Batches'[Status]) ) VAR CalendarStart = MIN('Calendar'[Date]) VAR CalendarEnd = MAX('Calendar'[Date]) VAR ProjectPeriod = IF( CalendarStart >= StartDate && CalendarEnd <= EndDate, 1, 0 ) VAR ProjectStatus = CALCULATE( MIN(Batches[Status]), ALLEXCEPT('Batches','Batches'[Status]) ) VAR Result = IF( ProjectPeriod = 0, BLANK(), SWITCH( TRUE(), ProjectStatus = "Manufactured", 1, ProjectStatus = "Plan", 2, ProjectStatus = "Cancelled", 3 ) ) RETURN Result
8 Replies
There is an active many to 1 relationship between Batches[ManufacturedDate) and Calendar[Date]. There is an inactive many to 1 relationship between Batches[LabelledExpiry] and Calendar[Date]
OKgo Can you post sample data or sample file? Need to understand where things are coming from in your visual.
I hope this meets the ask:
It is coming from a SharePoint list the manufacturing date and labelled expiry date are the start and end of a the gantt bar. The row context is batch ID and status. The columns from the calendar table. And the mesure is of course in the value
Batch ID Product Status Manufacturing Date Labelled Expiry Manufactured Quantity 111 Expensive Plan 3/8/2023 3/8/2024 1,122 222 Cheap Manufactured 3/17/2023 3/17/2025 5,000
