Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
GCH_FR
Frequent Visitor

How to do a calculation between date variable

‎07-17-2024 11:03 PM

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

Labels:
Message 1 of 11
452 Views
0
1 ACCEPTED SOLUTION
v-stephen-msft
Community Support
Community Support

‎07-29-2024 02:18 AM

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

 

vstephenmsft_0-1722244199155.png

You can create table visual to check data:

vstephenmsft_1-1722244494844.png

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?

vstephenmsft_2-1722244522352.png

 

 

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.

 

View solution in original post

sample29517.pbix
Message 8 of 11
288 Views
0
10 REPLIES 10
v-stephen-msft
Community Support
Community Support

‎07-29-2024 02:18 AM

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

 

vstephenmsft_0-1722244199155.png

You can create table visual to check data:

vstephenmsft_1-1722244494844.png

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?

vstephenmsft_2-1722244522352.png

 

 

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.

 

sample29517.pbix
Message 8 of 11
289 Views
0
GCH_FR
Frequent Visitor
In response to v-stephen-msft

‎07-30-2024 12:40 AM

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
Message 11 of 11
259 Views
0
GCH_FR
Frequent Visitor
In response to v-stephen-msft

‎07-29-2024 07:52 AM

 

Hello @v-stephen-msft 

 

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.

 

Message 9 of 11
278 Views
0
GCH_FR
Frequent Visitor
In response to GCH_FR

‎07-29-2024 10:35 PM

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"))
 
Message 10 of 11
253 Views
0
GCH_FR
Frequent Visitor

‎07-17-2024 11:45 PM

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
Message 6 of 11
423 Views
0
bhanu_gautam
Super User
Super User
In response to GCH_FR

‎07-17-2024 11:52 PM

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




Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!


LinkedIn






Message 7 of 11
420 Views
0
GCH_FR
Frequent Visitor

‎07-17-2024 11:34 PM

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...
Message 3 of 11
430 Views
0
bhanu_gautam
Super User
Super User
In response to GCH_FR

‎07-17-2024 11:41 PM

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




Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!


LinkedIn






Message 4 of 11
424 Views
GCH_FR
Frequent Visitor
In response to bhanu_gautam

‎07-23-2024 11:12 PM

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

 

GCH_FR_0-1721801084457.png

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. 
Message 5 of 11
350 Views
0
bhanu_gautam
Super User
Super User

‎07-17-2024 11:11 PM

@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]
)




Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!


LinkedIn






Message 2 of 11
441 Views

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.

View All