Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
GCH_FR
Frequent Visitor

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

1 ACCEPTED SOLUTION
v-stephen-msft
Community Support
Community Support

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

10 REPLIES 10
v-stephen-msft
Community Support
Community Support

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.

 

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

 

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.

 

GCH_FR
Frequent Visitor

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
Frequent Visitor

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

@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






GCH_FR
Frequent Visitor

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...

@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






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. 
bhanu_gautam
Super User
Super User

@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






Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

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! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.