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.

     

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.