Forum Discussion

GCH_FR's avatar
GCH_FR
Helper I
2 years ago
Solved

How to do a calculation between date variable

Hello Here,

 

I've got some problem with how to create a good calculation, let me explain my problem : 

 

I've got a table name "FUP"

 

DATE DEBUT ACTION BPATEMPS THEORIQUE BPA PAR JOURDATE FIN ACTION BPA
25/01/20241,5431/10/2024
05/01/20243,1726/01/2024
25/01/20241,9015/02/2024
25/01/20241,9015/02/2024
16/01/20241,9006/02/2024
16/01/20241,9106/02/2024
16/01/20241,9218/01/2024
19/01/20241,9309/02/2024

 

I make in Power BI a calendar with this all i want is that 

CALCUL FUP TEMPS THEORIQUE =
CALENDAR(MIN( 'DATE_PLANNING'[DATEPLANNING]),MAX( 'DATE_PLANNING'[DATEPLANNING]))
With the date inside Calcul a new column or measure with this, 

16/01/2024 = 1.9 + 1.91+1.92

18/01/2024 = 1.9 +1.91+1.92

19/01/2024 = 1.9 +1.91+1.93

20/01/2024 = 1.9 +1.91+1.93

etc...

 

I try some calcalutation but i don't arrive to a good result. how I can achieve this result

Can you help me please, i will be grateful

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi GCH_FR ,

     

    bhanu_gautam 's workaround is vaild, but somehow it doesn't work.

    Please allow me to share my insights.

    Create this calculated table.

     

    Table 2 = FILTER(CROSSJOIN(CALENDAR(MIN('Table'[DATE DEBUT ACTION BPA]),MAX('Table'[DATE FIN ACTION BPA])),'Table'),[Date]>=[DATE DEBUT ACTION BPA]&&[Date]<=[DATE FIN ACTION BPA])

     

    You can create table visual to check data:

    I have a question, I select the 1/16/2024 rows of [Date], and I found that 4 values match, but the expected result you provided was only 1.9 + 1.91 + 1.92, please why doesn't 3.17 belong?

     

     

    Best Regards,

    Stephen Tao

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

10 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    2 years ago

    GCH_FR , Make sure you have one date table

     

    DateTable = CALENDAR(MIN('FUP'[DATE DEBUT ACTION BPA]), MAX('FUP'[DATE FIN ACTION BPA]))

     

    Then create a measure to calulate the cumulative "TEMPS THEORIQUE BPA PAR JOUR" for each date

     

    CumulativeTempsTheorique =
    VAR CurrentDate = MAX('DateTable'[Date])
    RETURN
    SUMX(
    FILTER(
    'FUP',
    'FUP'[DATE DEBUT ACTION BPA] <= CurrentDate &&
    'FUP'[DATE FIN ACTION BPA] >= CurrentDate
    ),
    'FUP'[TEMPS THEORIQUE BPA PAR JOUR]
    )

  • GCH_FR's avatar
    GCH_FR
    Helper I
    2 years ago

    I've got 2 another questions

     

    I've got others conditions in this measure : 

     

    MEASURE TEMPS THEO CONCEPTION =
    VAR CurrentDate = MAX('CALCUL FUP TEMPS THEORIQUE'[Date])
    RETURN
    SUMX(
    FILTER(
    'FUP',
    'FUP'[CODIFICATION]="2-CONCEPTION"&&'FUP'[BUDGET BPA]<>0&&'FUP'[DATE DEBUT ACTION BPA] <= CurrentDate &&
    'FUP'[DATE FIN ACTION BPA] >= CurrentDate
    ),
    'FUP'[TEMPS THEORIQUE BPA PAR SEMAINE]
    )
    But it seem not working ...
     
    And after how to have cumulative Time like CUMULATED TIME IN BPA = Day 1 = Day 1, Day 2 = Day 2 + Day 1 etc...
    • bhanu_gautam's avatar
      bhanu_gautam
      Super User
      2 years ago

      GCH_FR , Update your measure

       

      MEASURE TEMPS THEO CONCEPTION =
      VAR CurrentDate = MAX('CALCUL FUP TEMPS THEORIQUE'[Date])
      RETURN
      SUMX(
      FILTER(
      'FUP',
      'FUP'[CODIFICATION] = "2-CONCEPTION" &&
      'FUP'[BUDGET BPA] <> 0 &&
      'FUP'[DATE DEBUT ACTION BPA] <= CurrentDate &&
      'FUP'[DATE FIN ACTION BPA] >= CurrentDate
      ),
      'FUP'[TEMPS THEORIQUE BPA PAR SEMAINE]
      )

       

      For cummulative calculated create one more measure

      CUMULATED TIME IN BPA =
      VAR CurrentDate = MAX('CALCUL FUP TEMPS THEORIQUE'[Date])
      RETURN
      CALCULATE(
      [MEASURE TEMPS THEO CONCEPTION],
      DATESBETWEEN(
      'CALCUL FUP TEMPS THEORIQUE'[Date],
      MIN('CALCUL FUP TEMPS THEORIQUE'[Date]),
      CurrentDate
      )
      )

      • GCH_FR's avatar
        GCH_FR
        Helper I
        2 years ago

        Sorry for the answer delay, i've got other thing to do.

         

        CUMULATED TIME IN BPA seem doesn't work.

        I've got the same number in each column

         

        TEST = CALENDAR(MIN('FUP'[DATE DEBUT ACTION BPA]), MAX('FUP'[DATE FIN ACTION BPA]))
        MEASURE TEMPS THEO CONCEPTION =
        VAR CurrentDate = MAX(TEST[Date])
        RETURN
        SUMX(
        FILTER(
        'FUP',
        'FUP'[CODIFICATION] = "2-CONCEPTION" &&
        'FUP'[BUDGET BPA] <> 0 &&
        'FUP'[DATE DEBUT ACTION BPA] <= CurrentDate &&
        'FUP'[DATE FIN ACTION BPA] >= CurrentDate
        ),
        'FUP'[TEMPS THEORIQUE BPA PAR JOUR]
        )
         
        CUMULATED TIME IN BPA =
        VAR CurrentDate = MAX(TEST[Date])
        RETURN
        CALCULATE(
        [MEASURE TEMPS THEO CONCEPTION],
        DATESBETWEEN(
        TEST[Date],
        MIN(TEST[Date]),
        CurrentDate
        )
        )
         
        I don't understand why. 
  • GCH_FR's avatar
    GCH_FR
    Helper I
    2 years ago

    Thanks for your answer, it's work well but how can i add some others conditions like that 

     

    MEASURE TEMPS THEO CONCEPTION =
    VAR CurrentDate = MAX('CALCUL FUP TEMPS THEORIQUE'[Date])
    RETURN
    SUMX(
    FILTER(
    'FUP',
    'FUP'[CODIFICATION]="2-CONCEPTION"&&'FUP'[DATE DEBUT ACTION BPA] <= CurrentDate &&
    'FUP'[DATE FIN ACTION BPA] >= CurrentDate
    ),
    'FUP'[TEMPS THEORIQUE BPA PAR SEMAINE]
    )
    It seem not work very well... I try with ALL() but not either.
     
    And after i can i do cumulative time like that : CUMULATE THEO = day 1 = day 1, day 2 = day 1 + Day 2
    • bhanu_gautam's avatar
      bhanu_gautam
      Super User
      2 years ago

      GCH_FR , I have already shared updated measure as per your condition and for cummulative also

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi GCH_FR ,

     

    bhanu_gautam 's workaround is vaild, but somehow it doesn't work.

    Please allow me to share my insights.

    Create this calculated table.

     

    Table 2 = FILTER(CROSSJOIN(CALENDAR(MIN('Table'[DATE DEBUT ACTION BPA]),MAX('Table'[DATE FIN ACTION BPA])),'Table'),[Date]>=[DATE DEBUT ACTION BPA]&&[Date]<=[DATE FIN ACTION BPA])

     

    You can create table visual to check data:

    I have a question, I select the 1/16/2024 rows of [Date], and I found that 4 values match, but the expected result you provided was only 1.9 + 1.91 + 1.92, please why doesn't 3.17 belong?

     

     

    Best Regards,

    Stephen Tao

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    • GCH_FR's avatar
      GCH_FR
      Helper I
      2 years ago

       

      Hello Anonymous 

       

      Thanks for taking time for me. To answer your question, it's just an oversight on my part on the 3.17.

       

      I'll try to take time tomorrow to test what you did and try to understand that. 

      Thanks a lot for your proposition.

      I'll try to explain a bit more precisely my problem because it's more complicated than what i show you.

       

      • GCH_FR's avatar
        GCH_FR
        Helper I
        2 years ago

        Hello everyone

        I'm going to try and clarify my problem:

        I have several important milestones: PRE / BPA / BPE with an action start date and an action end date.

        The final goal would be to compile by ‘CODIFICATION’ the PRE / BPA / BPE time per week and then the cumulative time per week.

        CODIFICATIONCODIFICATION 2NUMERO DOCUMENTDESIGNATIONDATE PREBUDGET PRELISSAGE PREDATE PRE FORCEEDATE DEBUT ACTION PRENO SEMAINE DEBUT ACTION PRETEMPS THEORIQUE PRE PAR JOURDATE FIN ACTION PREDATE BPABUDGET BPALISSAGE BPADATE BPA FORCEEDATE DEBUT ACTION BPANO SEMAINE DEBUT ACTION BPATEMPS THEORIQUE BPA PAR JOURDATE FIN ACTION BPADATE BPEBUDGET BPELISSAGE BPEDATE BPE FORCEEDATE DEBUT ACTION BPETEMPS THEORIQUE BPE PAR JOURDATE FIN ACTION BPE
        1-ORGANISATIONORGANISATIONORGASUIVI30/12/2023102 30/12/2023 0,7142857113/01/202431/10/202443040 25/01/202451,5357142931/10/2024       
        2-CONCEPTIONSCHÉMAS DRD LOT840YSC345HVAC - SCHEMAS GEOGRAPHIQUES - DRD - LOT 3450      26/01/202466,553 05/01/202423,1690476226/01/202415/03/2024153 23/02/20240,7142857115/03/2024
        2-CONCEPTIONSCHÉMAS DRD LOT840YSC453HVAC - SCHEMAS GEOGRAPHIQUES - DRD - LOT 4530      15/02/2024403 25/01/202451,904761915/02/202429/03/2024153 08/03/20240,7142857129/03/2024
        2-CONCEPTIONSCHÉMAS DRD LOT840YSC463HVAC - SCHEMAS GEOGRAPHIQUES - DRD - LOT 4630      15/02/2024403 25/01/202451,904761915/02/202429/03/2024153 08/03/20240,7142857129/03/2024
        2-CONCEPTIONSCHÉMAS DRD LOT840YSC462HVAC - SCHEMAS GEOGRAPHIQUES - DRD - LOT 4620      06/02/2024403 16/01/202441,904761906/02/202421/03/2024153 29/02/20240,7142857121/03/2024
        2-CONCEPTIONSCHÉMAS DRD LOT840YSC335HVAC - SCHEMAS GEOGRAPHIQUES - DRD - LOT 3350      06/02/2024403 16/01/202441,904761906/02/202420/03/2024153 28/02/20240,7142857120/03/2024
        2-CONCEPTIONSCHÉMAS DRD LOT840YSC344HVAC - SCHEMAS GEOGRAPHIQUES - DRD - LOT 3440      06/02/2024403 16/01/202441,904761906/02/202420/03/2024153 28/02/20240,7142857120/03/2024
        2-CONCEPTIONSCHÉMAS DRD LOT840YSC606HVAC - SCHEMAS GEOGRAPHIQUES - DRD - LOT 6060      09/02/2024403 19/01/202441,904761909/02/202429/03/2024153 08/03/20240,7142857129/03/2024
        2-CONCEPTIONNOMENCLATURE840YBM03HVAC - EQUIPMENTS IN ACCOMODATIONS0      26/01/20244,41 19/01/202440,6285714326/01/202422/11/2024444 25/10/20241,5714285722/11/2024
        2-CONCEPTIONNOMENCLATURE840YBM01HVAC - MAIN EQUIPMENTS0      26/01/20244,41 19/01/202440,6285714326/01/202422/11/2024444 25/10/20241,5714285722/11/2024
        2-CONCEPTIONNOMENCLATURE840YBM02HVAC - IL/OL 0      26/01/20244,41 19/01/202440,6285714326/01/202422/11/2024444 25/10/20241,5714285722/11/2024
        2-CONCEPTIONNOMENCLATURE840YBM04HVAC - EQUIPMENTS IN ACR0      26/01/20244,41 19/01/202440,6285714326/01/202422/11/2024444 25/10/20241,5714285722/11/2024
        2-CONCEPTIONNOMENCLATURE840YBM05HVAC - SEATINGS 0      26/01/20244,41 19/01/202440,6285714326/01/202422/11/2024444 25/10/20241,5714285722/11/2024
        2-CONCEPTIONNOMENCLATURE840YBM06HVAC - MISCELLANEOUS ELECTRICAL EQUIPMENTS0      26/01/20244,41 19/01/202440,6285714326/01/202422/11/2024444 25/10/20241,5714285722/11/2024
        2-CONCEPTIONSCHÉMAS PID TU862YACS462HHVAC - PID TUYAUTAGE (VFC) EN ACR  462H0      06/02/20242,51 30/01/202460,3571428606/02/202421/03/20242,51 14/03/20240,3571428621/03/2024
        2-CONCEPTIONSCHÉMAS PID TU863YACS462HHVAC - PID TUYAUTAGE (VFE) EN ACR  462H0      06/02/20242,51 30/01/202460,3571428606/02/202421/03/20242,51 14/03/20240,3571428621/03/2024

        Explanation sample 

        On Excel I have no problem compiling all this with the sumprod function.

         

        For the cumulative function, i've try this which seem working : 

         

        SUMX(
            FILTER(
                ALLSELECTED(FUP TEMPS THEORIQUE BPA),
                'FUP TEMPS THEORIQUE BPA'[DATE BPA]<=MAXX('FUP TEMPS THEORIQUE BPA',
                'FUP TEMPS THEORIQUE BPA'[DATE BPA])),
            'FUP TEMPS THEORIQUE BPA'[TEMPS THEORIQUE PAR JOUR BPA - 2-CONCEPTION])
         
        my auto calendar is like this
         
        CALENDRIER =
        VAR DebDate = MIN( 'DATE_PLANNING'[DATEPLANNING])
        VAR FinDate = MAX( 'DATE_PLANNING'[DATEPLANNING])
         
        RETURN
        ADDCOLUMNS(
          // Créer un première colonne date entre les dates de début de planning et la date de fin de planning  
            CALENDAR(DebDate,FinDate),
                    // Colonne Année dans Planning
                   "Annee", FORMAT([Date],"YYYY"),
                   // Colonne Numéro du mois dans Planning
                   "Numero du mois", MONTH([Date]),
                   // Colonne Semaine_ISO dans Planning : Mise en place de la semaine ISO 8601 (format standard calendrier européen): (WEEKNUM([Date];21)
                   "Semaine ISO" , "S" & FORMAT(WEEKNUM([Date],21),"00") ,
                   // Colonne Année_Sem dans Planning : Format : ANNEE_SXX (01/02/etc..). Mise en place de la colonne Annee_Sem ISO (pour le cumul hebdomadaire)
                   "Annee_Sem" , VAR An = YEAR([Date])
                                     VAR Sem = WEEKNUM([Date],2)
                                     VAR SemISO = WEEKNUM([Date],21) RETURN
                                    IF(
                                        AND (SemISO < 5, Sem > 50),
                                        An+1,
                                        IF(
                                            AND (SemISO > 50, Sem < 5),
                                            An-1,
                                            An
                                        )
                                    )
                                    & "-S" & FORMAT(SemISO,"00"),
                    // Colonne Semaine en Cours dans Planning : Format : VRAI, FAUX. Indique vrai / faux pour semaine en cours. !!!
                    "Semaine_en_cours" , IF(
                                    VAR An = YEAR([Date])
                                     VAR Sem = WEEKNUM([Date],2)
                                     VAR SemISO = WEEKNUM([Date],21) RETURN
                                    IF(
                                        AND (SemISO < 5, Sem > 50),
                                        An+1,
                                        IF(
                                            AND (SemISO > 50, Sem < 5),
                                            An-1,
                                            An
                                        )
                                    )
                                    &SemISO=
                                VAR AnToday = YEAR(TODAY())                      
                                VAR SemToday = WEEKNUM(TODAY(),2)
                                  VAR SemIsoToday = WEEKNUM(TODAY(),21) RETURN
                                    IF(
                                        AND (SemIsoToday < 5, SemToday > 50),
                                        AnToday+1,
                                        IF(
                                            AND (SemIsoToday > 50, SemToday < 5),
                                            AnToday-1,
                                            AnToday
                                        )
                                    )
                                    &SemIsoToday
                                    ,"VRAI","FAUX"))
         
    • GCH_FR's avatar
      GCH_FR
      Helper I
      2 years ago

      Hello @v-stephen-msft 

       

      To explain my problem in more detail

      I have several milestones that can be chained to each other in relation to PRE / BPE / BPE action start dates and PRE / BPE / BPE action end dates, so as to have times per day / week by “CODIFICATION” and then in cumulative times, also by “CODIFICATION”.

       

      About the cumulative time per day, it's seem working with this one : 

       

      TEMPS THEORIQUE CUMULE BPA - 2-CONCEPTION =

      SUMX(
          FILTER(
              ALLSELECTED('CALCUL FUP TEMPS THEORIQUE BPA'),
              'CALCUL FUP TEMPS THEORIQUE BPA'[DATE BPA]<=MAXX('1-CALCUL FUP TEMPS THEORIQUE BPA',
              'CALCUL FUP TEMPS THEORIQUE BPA'[DATE BPA])),
          CALCUL FUP TEMPS THEORIQUE BPA'[TEMPS THEORIQUE PAR JOUR BPA - 2-CONCEPTION])
       
      Here my Table : 
       
      CODIFICATIONCODIFICATION 2NUMERO DOCUMENTDESIGNATIONDATE PREBUDGET PRELISSAGE PREDATE PRE FORCEEDATE DEBUT ACTION PRENO SEMAINE DEBUT ACTION PRETEMPS THEORIQUE PRE PAR JOURDATE FIN ACTION PREDATE BPABUDGET BPALISSAGE BPADATE BPA FORCEEDATE DEBUT ACTION BPANO SEMAINE DEBUT ACTION BPATEMPS THEORIQUE BPA PAR JOURDATE FIN ACTION BPADATE BPEBUDGET BPELISSAGE BPEDATE BPE FORCEEDATE DEBUT ACTION BPETEMPS THEORIQUE BPE PAR JOURDATE FIN ACTION BPE
      1-ORGANISATIONORGANISATIONORGASUIVI30/12/2023102 30/12/2023 0,7142857113/01/202431/10/202443040 25/01/202451,5357142931/10/2024       
      2-CONCEPTIONSCHÉMAS DRD LOT840YSC345HVAC - SCHEMAS GEOGRAPHIQUES - DRD - LOT 3450      26/01/202466,553 05/01/202423,1690476226/01/202415/03/2024153 23/02/20240,7142857115/03/2024
      2-CONCEPTIONSCHÉMAS DRD LOT840YSC453HVAC - SCHEMAS GEOGRAPHIQUES - DRD - LOT 4530      15/02/2024403 25/01/202451,904761915/02/202429/03/2024153 08/03/20240,7142857129/03/2024
      2-CONCEPTIONSCHÉMAS DRD LOT840YSC463HVAC - SCHEMAS GEOGRAPHIQUES - DRD - LOT 4630      15/02/2024403 25/01/202451,904761915/02/202429/03/2024153 08/03/20240,7142857129/03/2024
      2-CONCEPTIONSCHÉMAS DRD LOT840YSC462HVAC - SCHEMAS GEOGRAPHIQUES - DRD - LOT 4620      06/02/2024403 16/01/202441,904761906/02/202421/03/2024153 29/02/20240,7142857121/03/2024
      2-CONCEPTIONSCHÉMAS DRD LOT840YSC335HVAC - SCHEMAS GEOGRAPHIQUES - DRD - LOT 3350      06/02/2024403 16/01/202441,904761906/02/202420/03/2024153 28/02/20240,7142857120/03/2024
      2-CONCEPTIONSCHÉMAS DRD LOT840YSC344HVAC - SCHEMAS GEOGRAPHIQUES - DRD - LOT 3440      06/02/2024403 16/01/202441,904761906/02/202420/03/2024153 28/02/20240,7142857120/03/2024
      2-CONCEPTIONSCHÉMAS DRD LOT840YSC606HVAC - SCHEMAS GEOGRAPHIQUES - DRD - LOT 6060      09/02/2024403 19/01/202441,904761909/02/202429/03/2024153 08/03/20240,7142857129/03/2024
      2-CONCEPTIONNOMENCLATURE840YBM03HVAC - EQUIPMENTS IN ACCOMODATIONS0      26/01/20244,41 19/01/202440,6285714326/01/202422/11/2024444 25/10/20241,5714285722/11/2024
      2-CONCEPTIONNOMENCLATURE840YBM01HVAC - MAIN EQUIPMENTS0      26/01/20244,41 19/01/202440,6285714326/01/202422/11/2024444 25/10/20241,5714285722/11/2024
      2-CONCEPTIONNOMENCLATURE840YBM02HVAC - IL/OL 0      26/01/20244,41 19/01/202440,6285714326/01/202422/11/2024444 25/10/20241,5714285722/11/2024
      2-CONCEPTIONNOMENCLATURE840YBM04HVAC - EQUIPMENTS IN ACR0      26/01/20244,41 19/01/202440,6285714326/01/202422/11/2024444 25/10/20241,5714285722/11/2024
      2-CONCEPTIONNOMENCLATURE840YBM05HVAC - SEATINGS 0      26/01/20244,41 19/01/202440,6285714326/01/202422/11/2024444 25/10/20241,5714285722/11/2024
      2-CONCEPTIONNOMENCLATURE840YBM06HVAC - MISCELLANEOUS ELECTRICAL EQUIPMENTS0      26/01/20244,41 19/01/202440,6285714326/01/202422/11/2024444 25/10/20241,5714285722/11/2024
      2-CONCEPTIONSCHÉMAS PID TU862YACS462HHVAC - PID TUYAUTAGE (VFC) EN ACR  462H0      06/02/20242,51 30/01/202460,3571428606/02/202421/03/20242,51 14/03/20240,3571428621/03/2024
      2-CONCEPTIONSCHÉMAS PID TU863YACS462HHVAC - PID TUYAUTAGE (VFE) EN ACR  462H0      06/02/20242,51 30/01/202460,3571428606/02/202421/03/20242,51 14/03/20240,3571428621/03/2024