Forum Discussion

LucasMascarellD's avatar
LucasMascarellD
Frequent Visitor
3 years ago

Schedule, Gantt

Hi,

I need some Help, I use Excel to create a schedule, like a Gantt Chart, Imagen:

 

 

I use this formula to calculate de cells : 
=SE(MÍNIMO($AB$1-SOMA(AD$8:AD19),$Z20-SOMA($AB20:AC20))=0,"",MÍNIMO($AB$1-SOMA(AD$8:AD19),$Z20-SOMA($AB20:AC20)))
SE = IF
Mínimo = Min
Soma = Sum
I need to do the same schedule on power BI.
I dont have StarDate and EndDate.
it´s need to calculate a daily production of 15000 units, from the current day, when the 15000 closes,  needs to switch to the next day with the balance of the previous day and count another 15000.

"DAX"
Gantt chart
schedule

4 Replies

  • PiEye's avatar
    PiEye
    Resolver II
    3 years ago

    Hi Lucas,

     

    I think we're going to need more information. While the excel formula is helpful, I can't see the column references for the cells.

    Can you tell me

    • What does the raw data look like? (or provide)
    • What is the calculation achieving & why, very broadly.

    While the detail calculation itself is useful (min / max sum etc) there is a much wider range of expressions in DAX and the resulting steps may be different and more optimal than just trying to replicate every part of an excel expression.

     

    PS Intendo portugues 🙂

     

    Pi

    • LucasMascarellD's avatar
      LucasMascarellD
      Frequent Visitor
      3 years ago

      I´m sorry, I rewrote the text, I thing it´s clearer now.

      I showing the expression that I use on Excel, because it was the way that a found to resolve.

       

  • PiEye's avatar
    PiEye
    Resolver II
    3 years ago

    Hi Lucas, I think I understand now.

     

    I am recreating with two sets of data, one is for OP & QTD and the other is just a list of dates:

    In DAX, we are unable to self-reference an expression. This means that you cannot refer back to a previous row within the same expression. 

    However, it is possible to arithmetically calculate the amounts that should be allocated to each OP & day, using combinations of cumulative and modulus functions. 

     

    Measures created

    CONST = 14700
Cumulative = sumX(
    WINDOW(
        1,ABS,0,REL,
        ORDERBY(gant[OP], ASC)
    ), 
    [Sum of Quantity]
)
Mod = mod([Cumulative],[CONST])
Quotient = QUOTIENT([Cumulative],[CONST])
quantity less mod = if([Sum of Quantity]  -[Mod]>0,[Sum of Quantity]  -[Mod])
Date that completes qty = Date(2023,06,03)  +[Quotient]

    I had to create a measure for the "constant" 14700

    this is what it looks like laid out in a table:

     

     

    We can then use the date that completes qty to calculate when o put the straight Qty amount in, and if it is the day before, use the Quantity less mod:

    Production day = switch( TRUE(),
     [Date that completes qty] = SELECTEDVALUE(DATES[DATE]),[Sum of Quantity],
     [Date that completes qty] - 1 = SELECTEDVALUE(DATES[DATE]),[quantity less mod])

     

    This results in a pivot table as such:

     

    How does this work?

    The cumulative amount is the total units that need to be produced in order of OP. We then take the quotient, to see how many times 14700 fits into it - this is the number of days until this would be complete. We can then use this to calculate dates and match to the dates in the column headings and fill with either the quantity or a remainder.

     

    There are other ways to do this, but this is simplest for now, assuming that the CONST might not be a constant or linked to some other value.

     

    HTH,

     

    Pi

     

     

     

     

  • PiEye's avatar
    PiEye
    Resolver II
    3 years ago

    If it helps, in excel this looks like:

     

     

    =IF($H37=J$34,$F37,IF(AND($H37=J$34+1,$G37>0),$G37,""))