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 BPA TEMPS THEORIQUE BPA PAR JOUR D...
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.

     

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"))