Forum Discussion

GCH_FR's avatar
GCH_FR
Icon for Helper I rankHelper I
1 year ago
Solved

SUMMARIZE WITH DATE AND OTHER CONDITONS

Hello everybody    I'm here to ask somme help :   I've got a table like that named "0-FUP" :  CODIFICATION BUDGET BPA LISSAGE BPA DATE BPA FORCEE DATE DEBUT ACTION BPA TEMPS THEORIQUE BP...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi GCH_FR 
    Thank you for reaching out microsoft fabric community forum.

    The cumulative calculation logic in your current setup may be producing incorrect results due to the use of the EARLIER function, which can create context-related issues when used in complex filters or nested row contexts. A more robust and reliable approach is to use the ALLEXCEPT function, which ensures that the cumulative sum is correctly calculated for each "CODIFICATION" by preserving its context while allowing the accumulation over time. For example, the DAX expression:

    CumulativeTimeBPA =
    CALCULATE (
        SUM ( [DailyTimeBPA] ),
        FILTER (
            ALLEXCEPT ( DailyTime, DailyTime[CODIFICATION] ),
            DailyTime[Date] <= EARLIER ( DailyTime[Date] )
        )
    )

    ensures that for each row, the sum includes all previous dates for the same "CODIFICATION" without being disrupted by unrelated filters. 


    If this solution helps, please consider giving us Kudos and accepting it as the solution so that it may assist other members in the community
    Thank you.

bhanu_gautam's avatar
bhanu_gautam
Icon for Super User rankSuper User
1 year ago

GCH_FR 

Create a date table that covers the range of dates in your data.

DateTable =
ADDCOLUMNS (
CALENDAR ( MIN('0-FUP'[DATE DEBUT ACTION BPA]), MAX('0-FUP'[DATE FIN ACTION BPA]) ),
"Year", YEAR([Date]),
"Month", MONTH([Date]),
"Day", DAY([Date]),
"YearWeek", YEAR([Date]) & "-S" & WEEKNUM([Date], 2)
)

 

Create a table that expands the date range for each action to daily records.

DailyRecords =
ADDCOLUMNS (
FILTER (
CROSSJOIN ( DateTable, '0-FUP' ),
[Date] >= '0-FUP'[DATE DEBUT ACTION BPA] && [Date] <= '0-FUP'[DATE FIN ACTION BPA]
),
"DailyTimeBPA", '0-FUP'[TEMPS THEORIQUE BPA PAR JOUR]
)

 

Calculate the theoretical time per day for each action.

DailyTime =
SUMMARIZE (
DailyRecords,
[Date],
'0-FUP'[CODIFICATION],
"DailyTimeBPA", SUM([DailyTimeBPA])
)

 

Calculate the cumulative theoretical time per day for each action.

DailyTimeWithCumulative =
ADDCOLUMNS (
DailyTime,
"CumulativeTimeBPA",
CALCULATE (
SUM ( [DailyTimeBPA] ),
FILTER (
ALL ( DailyTime ),
DailyTime[CODIFICATION] = EARLIER ( DailyTime[CODIFICATION] ) &&
DailyTime[Date] <= EARLIER ( DailyTime[Date] )
)
)
)

 

Group the data by year and week to get the desired summary.

WeeklySummary =
SUMMARIZE (
DailyTimeWithCumulative,
[YearWeek],
[CODIFICATION],
"WeeklyTimeBPA", SUM([DailyTimeBPA]),
"CumulativeTimeBPA", MAX([CumulativeTimeBPA])
)

GCH_FR's avatar
GCH_FR
Icon for Helper I rankHelper I
1 year ago

bhanu_gautam 

Your answer work, but i've got a new problem : 

 

here is a summary of my total time for BPA or BPE :

CODIFICATIONBUDGET TOTAL HEURE BPABUDGET TOTAL HEURE BPE
1-ORGANISATION / GESTION / SUIVI ETUDES786.893681
2-CONCEPTION2352,056915,69
3-PLANS03711,75
4-CONSULTATION / SPEC TECHNIQUES / COMMANDES68,51264,9
5-ELECTRICITE / REGULATION716,854516,67
6-DOE0644
7-DIVERS080
8-DEPLACEMENT00

 

 

 

 

In Codification "1-ORGANISATION" i I find the same result as in the table above

but for the other references I have quite a few differences when the result should be identical

 

And i don't understand why, do you have any ideas ??