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.

     

GCH_FR's avatar
GCH_FR
Helper I
2 years ago

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