Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Struggling with Line and Clustered Column chart

Hello Everyone,

I need some help with this problem. I would like to display all the Lots in a Line and Clustered Column Chart with columns for open and closed per month and the lines displaying cumulative open and closed values. 

 

I have a Data table with Lot No, Status Title, Created Date, Opened Date and Closed Date if Closed.

I also have a Date table with MonthYear. I have linked the Tables using the "Date" > "Created On" date in the Data Table and can get the Visual to display the values but not as expected. Power Bi is display the number open correctly but only displaying the number closed based on the Create date. Example. July actually has 182 closed but only displays 6 as only 6 where created in July.

Any help would be appreciated.

 

Thank you.

 

Need Help

1 Reply

  • freginier's avatar
    freginier
    Icon for Solution Sage rankSolution Sage
    1 year ago

    Hi there! I think I can help with your problem. If I understood correctly, you are trying to build a Line and Clustered Column Chart in Power BI where columns show lots opened vs. closed per month and lines show cumulative opened vs. closed, but the closed lots are being counted based on created on and not closed date. 

    Instead of linking your Date Table to "Created On", create a new inactive relationship with "Closed Date".
    You can go to Model View and then create a relationship between Date[MonthYear] and Data[Lot Closed On]. Don't activate it yet. 
    Then make 4 different DAX formulas: 

    1. Lots opened per month: LotsOpened = COUNT(Data[Lot No])

    2. Lots closed per month: LotsClosed =
    CALCULATE(
    COUNT(Data[Lot No]),
    USERELATIONSHIP(Date[MonthYear], Data[Lot Closed On])
    )

    3. Cumulative Lots Opened: Cum_LotsOpened =
    CALCULATE(
    [LotsOpened],
    FILTER(ALLSELECTED(Date[MonthYear]), Date[MonthYear] <= MAX(Date[MonthYear]))
    )

    4. Cumulative Lots Closed: Cum_LotsClosed =
    CALCULATE(
    [LotsClosed],
    FILTER(ALLSELECTED(Date[MonthYear]), Date[MonthYear] <= MAX(Date[MonthYear]))
    )

    Then just set up your visual! With monthyear as the axis, LotsOpened & LotsClosed for the column and Cum_LotsOpened & Cum_LotsClosed as the Lines. 

     

    Hope this helps you!

    Zoe ðŸ˜€