Gantt Chart with Matrix Visual

[OKgo]

Saw this cool post and decided to try it out
https://www.linkedin.com/posts/basdohmen_powerbi-design-activity-7172551962473795584-fPP_?utm_source...

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]

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]

[Greg_Deckler]

@OKgo Can you post sample data or sample file? Need to understand where things are coming from in your visual.

[OKgo]

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

[Greg_Deckler]

@OKgo OK, so are you saying that Plan should go from 3/8/2023 to 3/8/2024 and manufacturing from 3/17/2023 to 3/17/2025 correct?

[OKgo]

yes thats it

[Greg_Deckler · Accepted solution]

@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

[OKgo]

Firstly fantastic filename!

The issue was the cross filter direction. You had set this to single and it worked when I applied the same logic to my model it worked. Thank you so much. This is for sure something I will read up more on. 

[Greg_Deckler]

@OKgo Lol! Yeah, that was definitely not a typo! Glad you got it figured out!