Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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 | DATE FIN ACTION BPA |
25/01/2024 | 1,54 | 31/10/2024 |
05/01/2024 | 3,17 | 26/01/2024 |
25/01/2024 | 1,90 | 15/02/2024 |
25/01/2024 | 1,90 | 15/02/2024 |
16/01/2024 | 1,90 | 06/02/2024 |
16/01/2024 | 1,91 | 06/02/2024 |
16/01/2024 | 1,92 | 18/01/2024 |
19/01/2024 | 1,93 | 09/02/2024 |
I make in Power BI a calendar with this all i want is that
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
Solved! Go to Solution.
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.
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.
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 :
CODIFICATION | CODIFICATION 2 | NUMERO DOCUMENT | DESIGNATION | DATE PRE | BUDGET PRE | LISSAGE PRE | DATE PRE FORCEE | DATE DEBUT ACTION PRE | NO SEMAINE DEBUT ACTION PRE | TEMPS THEORIQUE PRE PAR JOUR | DATE FIN ACTION PRE | DATE BPA | BUDGET BPA | LISSAGE BPA | DATE BPA FORCEE | DATE DEBUT ACTION BPA | NO SEMAINE DEBUT ACTION BPA | TEMPS THEORIQUE BPA PAR JOUR | DATE FIN ACTION BPA | DATE BPE | BUDGET BPE | LISSAGE BPE | DATE BPE FORCEE | DATE DEBUT ACTION BPE | TEMPS THEORIQUE BPE PAR JOUR | DATE FIN ACTION BPE |
1-ORGANISATION | ORGANISATION | ORGA | SUIVI | 30/12/2023 | 10 | 2 | 30/12/2023 | 0,71428571 | 13/01/2024 | 31/10/2024 | 430 | 40 | 25/01/2024 | 5 | 1,53571429 | 31/10/2024 | ||||||||||
2-CONCEPTION | SCHÉMAS DRD LOT | 840YSC345 | HVAC - SCHEMAS GEOGRAPHIQUES - DRD - LOT 345 | 0 | 26/01/2024 | 66,55 | 3 | 05/01/2024 | 2 | 3,16904762 | 26/01/2024 | 15/03/2024 | 15 | 3 | 23/02/2024 | 0,71428571 | 15/03/2024 | |||||||||
2-CONCEPTION | SCHÉMAS DRD LOT | 840YSC453 | HVAC - SCHEMAS GEOGRAPHIQUES - DRD - LOT 453 | 0 | 15/02/2024 | 40 | 3 | 25/01/2024 | 5 | 1,9047619 | 15/02/2024 | 29/03/2024 | 15 | 3 | 08/03/2024 | 0,71428571 | 29/03/2024 | |||||||||
2-CONCEPTION | SCHÉMAS DRD LOT | 840YSC463 | HVAC - SCHEMAS GEOGRAPHIQUES - DRD - LOT 463 | 0 | 15/02/2024 | 40 | 3 | 25/01/2024 | 5 | 1,9047619 | 15/02/2024 | 29/03/2024 | 15 | 3 | 08/03/2024 | 0,71428571 | 29/03/2024 | |||||||||
2-CONCEPTION | SCHÉMAS DRD LOT | 840YSC462 | HVAC - SCHEMAS GEOGRAPHIQUES - DRD - LOT 462 | 0 | 06/02/2024 | 40 | 3 | 16/01/2024 | 4 | 1,9047619 | 06/02/2024 | 21/03/2024 | 15 | 3 | 29/02/2024 | 0,71428571 | 21/03/2024 | |||||||||
2-CONCEPTION | SCHÉMAS DRD LOT | 840YSC335 | HVAC - SCHEMAS GEOGRAPHIQUES - DRD - LOT 335 | 0 | 06/02/2024 | 40 | 3 | 16/01/2024 | 4 | 1,9047619 | 06/02/2024 | 20/03/2024 | 15 | 3 | 28/02/2024 | 0,71428571 | 20/03/2024 | |||||||||
2-CONCEPTION | SCHÉMAS DRD LOT | 840YSC344 | HVAC - SCHEMAS GEOGRAPHIQUES - DRD - LOT 344 | 0 | 06/02/2024 | 40 | 3 | 16/01/2024 | 4 | 1,9047619 | 06/02/2024 | 20/03/2024 | 15 | 3 | 28/02/2024 | 0,71428571 | 20/03/2024 | |||||||||
2-CONCEPTION | SCHÉMAS DRD LOT | 840YSC606 | HVAC - SCHEMAS GEOGRAPHIQUES - DRD - LOT 606 | 0 | 09/02/2024 | 40 | 3 | 19/01/2024 | 4 | 1,9047619 | 09/02/2024 | 29/03/2024 | 15 | 3 | 08/03/2024 | 0,71428571 | 29/03/2024 | |||||||||
2-CONCEPTION | NOMENCLATURE | 840YBM03 | HVAC - EQUIPMENTS IN ACCOMODATIONS | 0 | 26/01/2024 | 4,4 | 1 | 19/01/2024 | 4 | 0,62857143 | 26/01/2024 | 22/11/2024 | 44 | 4 | 25/10/2024 | 1,57142857 | 22/11/2024 | |||||||||
2-CONCEPTION | NOMENCLATURE | 840YBM01 | HVAC - MAIN EQUIPMENTS | 0 | 26/01/2024 | 4,4 | 1 | 19/01/2024 | 4 | 0,62857143 | 26/01/2024 | 22/11/2024 | 44 | 4 | 25/10/2024 | 1,57142857 | 22/11/2024 | |||||||||
2-CONCEPTION | NOMENCLATURE | 840YBM02 | HVAC - IL/OL | 0 | 26/01/2024 | 4,4 | 1 | 19/01/2024 | 4 | 0,62857143 | 26/01/2024 | 22/11/2024 | 44 | 4 | 25/10/2024 | 1,57142857 | 22/11/2024 | |||||||||
2-CONCEPTION | NOMENCLATURE | 840YBM04 | HVAC - EQUIPMENTS IN ACR | 0 | 26/01/2024 | 4,4 | 1 | 19/01/2024 | 4 | 0,62857143 | 26/01/2024 | 22/11/2024 | 44 | 4 | 25/10/2024 | 1,57142857 | 22/11/2024 | |||||||||
2-CONCEPTION | NOMENCLATURE | 840YBM05 | HVAC - SEATINGS | 0 | 26/01/2024 | 4,4 | 1 | 19/01/2024 | 4 | 0,62857143 | 26/01/2024 | 22/11/2024 | 44 | 4 | 25/10/2024 | 1,57142857 | 22/11/2024 | |||||||||
2-CONCEPTION | NOMENCLATURE | 840YBM06 | HVAC - MISCELLANEOUS ELECTRICAL EQUIPMENTS | 0 | 26/01/2024 | 4,4 | 1 | 19/01/2024 | 4 | 0,62857143 | 26/01/2024 | 22/11/2024 | 44 | 4 | 25/10/2024 | 1,57142857 | 22/11/2024 | |||||||||
2-CONCEPTION | SCHÉMAS PID TU | 862YACS462H | HVAC - PID TUYAUTAGE (VFC) EN ACR 462H | 0 | 06/02/2024 | 2,5 | 1 | 30/01/2024 | 6 | 0,35714286 | 06/02/2024 | 21/03/2024 | 2,5 | 1 | 14/03/2024 | 0,35714286 | 21/03/2024 | |||||||||
2-CONCEPTION | SCHÉMAS PID TU | 863YACS462H | HVAC - PID TUYAUTAGE (VFE) EN ACR 462H | 0 | 06/02/2024 | 2,5 | 1 | 30/01/2024 | 6 | 0,35714286 | 06/02/2024 | 21/03/2024 | 2,5 | 1 | 14/03/2024 | 0,35714286 | 21/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.
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.
CODIFICATION | CODIFICATION 2 | NUMERO DOCUMENT | DESIGNATION | DATE PRE | BUDGET PRE | LISSAGE PRE | DATE PRE FORCEE | DATE DEBUT ACTION PRE | NO SEMAINE DEBUT ACTION PRE | TEMPS THEORIQUE PRE PAR JOUR | DATE FIN ACTION PRE | DATE BPA | BUDGET BPA | LISSAGE BPA | DATE BPA FORCEE | DATE DEBUT ACTION BPA | NO SEMAINE DEBUT ACTION BPA | TEMPS THEORIQUE BPA PAR JOUR | DATE FIN ACTION BPA | DATE BPE | BUDGET BPE | LISSAGE BPE | DATE BPE FORCEE | DATE DEBUT ACTION BPE | TEMPS THEORIQUE BPE PAR JOUR | DATE FIN ACTION BPE |
1-ORGANISATION | ORGANISATION | ORGA | SUIVI | 30/12/2023 | 10 | 2 | 30/12/2023 | 0,71428571 | 13/01/2024 | 31/10/2024 | 430 | 40 | 25/01/2024 | 5 | 1,53571429 | 31/10/2024 | ||||||||||
2-CONCEPTION | SCHÉMAS DRD LOT | 840YSC345 | HVAC - SCHEMAS GEOGRAPHIQUES - DRD - LOT 345 | 0 | 26/01/2024 | 66,55 | 3 | 05/01/2024 | 2 | 3,16904762 | 26/01/2024 | 15/03/2024 | 15 | 3 | 23/02/2024 | 0,71428571 | 15/03/2024 | |||||||||
2-CONCEPTION | SCHÉMAS DRD LOT | 840YSC453 | HVAC - SCHEMAS GEOGRAPHIQUES - DRD - LOT 453 | 0 | 15/02/2024 | 40 | 3 | 25/01/2024 | 5 | 1,9047619 | 15/02/2024 | 29/03/2024 | 15 | 3 | 08/03/2024 | 0,71428571 | 29/03/2024 | |||||||||
2-CONCEPTION | SCHÉMAS DRD LOT | 840YSC463 | HVAC - SCHEMAS GEOGRAPHIQUES - DRD - LOT 463 | 0 | 15/02/2024 | 40 | 3 | 25/01/2024 | 5 | 1,9047619 | 15/02/2024 | 29/03/2024 | 15 | 3 | 08/03/2024 | 0,71428571 | 29/03/2024 | |||||||||
2-CONCEPTION | SCHÉMAS DRD LOT | 840YSC462 | HVAC - SCHEMAS GEOGRAPHIQUES - DRD - LOT 462 | 0 | 06/02/2024 | 40 | 3 | 16/01/2024 | 4 | 1,9047619 | 06/02/2024 | 21/03/2024 | 15 | 3 | 29/02/2024 | 0,71428571 | 21/03/2024 | |||||||||
2-CONCEPTION | SCHÉMAS DRD LOT | 840YSC335 | HVAC - SCHEMAS GEOGRAPHIQUES - DRD - LOT 335 | 0 | 06/02/2024 | 40 | 3 | 16/01/2024 | 4 | 1,9047619 | 06/02/2024 | 20/03/2024 | 15 | 3 | 28/02/2024 | 0,71428571 | 20/03/2024 | |||||||||
2-CONCEPTION | SCHÉMAS DRD LOT | 840YSC344 | HVAC - SCHEMAS GEOGRAPHIQUES - DRD - LOT 344 | 0 | 06/02/2024 | 40 | 3 | 16/01/2024 | 4 | 1,9047619 | 06/02/2024 | 20/03/2024 | 15 | 3 | 28/02/2024 | 0,71428571 | 20/03/2024 | |||||||||
2-CONCEPTION | SCHÉMAS DRD LOT | 840YSC606 | HVAC - SCHEMAS GEOGRAPHIQUES - DRD - LOT 606 | 0 | 09/02/2024 | 40 | 3 | 19/01/2024 | 4 | 1,9047619 | 09/02/2024 | 29/03/2024 | 15 | 3 | 08/03/2024 | 0,71428571 | 29/03/2024 | |||||||||
2-CONCEPTION | NOMENCLATURE | 840YBM03 | HVAC - EQUIPMENTS IN ACCOMODATIONS | 0 | 26/01/2024 | 4,4 | 1 | 19/01/2024 | 4 | 0,62857143 | 26/01/2024 | 22/11/2024 | 44 | 4 | 25/10/2024 | 1,57142857 | 22/11/2024 | |||||||||
2-CONCEPTION | NOMENCLATURE | 840YBM01 | HVAC - MAIN EQUIPMENTS | 0 | 26/01/2024 | 4,4 | 1 | 19/01/2024 | 4 | 0,62857143 | 26/01/2024 | 22/11/2024 | 44 | 4 | 25/10/2024 | 1,57142857 | 22/11/2024 | |||||||||
2-CONCEPTION | NOMENCLATURE | 840YBM02 | HVAC - IL/OL | 0 | 26/01/2024 | 4,4 | 1 | 19/01/2024 | 4 | 0,62857143 | 26/01/2024 | 22/11/2024 | 44 | 4 | 25/10/2024 | 1,57142857 | 22/11/2024 | |||||||||
2-CONCEPTION | NOMENCLATURE | 840YBM04 | HVAC - EQUIPMENTS IN ACR | 0 | 26/01/2024 | 4,4 | 1 | 19/01/2024 | 4 | 0,62857143 | 26/01/2024 | 22/11/2024 | 44 | 4 | 25/10/2024 | 1,57142857 | 22/11/2024 | |||||||||
2-CONCEPTION | NOMENCLATURE | 840YBM05 | HVAC - SEATINGS | 0 | 26/01/2024 | 4,4 | 1 | 19/01/2024 | 4 | 0,62857143 | 26/01/2024 | 22/11/2024 | 44 | 4 | 25/10/2024 | 1,57142857 | 22/11/2024 | |||||||||
2-CONCEPTION | NOMENCLATURE | 840YBM06 | HVAC - MISCELLANEOUS ELECTRICAL EQUIPMENTS | 0 | 26/01/2024 | 4,4 | 1 | 19/01/2024 | 4 | 0,62857143 | 26/01/2024 | 22/11/2024 | 44 | 4 | 25/10/2024 | 1,57142857 | 22/11/2024 | |||||||||
2-CONCEPTION | SCHÉMAS PID TU | 862YACS462H | HVAC - PID TUYAUTAGE (VFC) EN ACR 462H | 0 | 06/02/2024 | 2,5 | 1 | 30/01/2024 | 6 | 0,35714286 | 06/02/2024 | 21/03/2024 | 2,5 | 1 | 14/03/2024 | 0,35714286 | 21/03/2024 | |||||||||
2-CONCEPTION | SCHÉMAS PID TU | 863YACS462H | HVAC - PID TUYAUTAGE (VFE) EN ACR 462H | 0 | 06/02/2024 | 2,5 | 1 | 30/01/2024 | 6 | 0,35714286 | 06/02/2024 | 21/03/2024 | 2,5 | 1 | 14/03/2024 | 0,35714286 | 21/03/2024 |
On Excel I have no problem compiling all this with the sumprod function.
For the cumulative function, i've try this which seem working :
Thanks for your answer, it's work well but how can i add some others conditions like that
@GCH_FR , I have already shared updated measure as per your condition and for cummulative also
Proud to be a Super User! |
|
I've got 2 another questions
I've got others conditions in this measure :
@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
)
)
Proud to be a Super User! |
|
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 , 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]
)
Proud to be a Super User! |
|
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
92 | |
82 | |
71 | |
49 |
User | Count |
---|---|
143 | |
121 | |
112 | |
58 | |
57 |