show the acumulative but only the total when is filtered

[JoseLuisAGZZ]

Hi yall hope yall having a great day

What im trying to achieve but dont know if its possible is this:

I have a database which is basically how many hours an employee spend in some of our projects for example

IDEmp     hours     project    type            date
  1               8             A          Regular     10/02/2022

  1               8             A          Regular     10/03/2022

  1               8             B          Extra          10/03/2022

  1               8             A          Extra          10/04/2022

  1               8             C          Extra          10/05/2022

So if filter a matrix with this data by project and date it will display 

something like this:

                 Dates    10/02/2022              10/03/2022             10/04/2022

Employee   
1                                  8                               8                            8

What Im trying to achieve is if theres an extra before, sum the previous hours only to the day that have the same criteria for example

In the day 4 I need to display 16 because the sum of all criteria Extra before, but not display the 8 in the day 3 because doesnt belong to the project A, only the acumulative sum of all extra criteria before

I know is kinda weird,but if someone have an idea how to achieve this I wil really apreciate that

thanks in advace

[v-yanjiang-msft · Accepted solution]

Hi @JoseLuisAGZZ ,

According to your description, here's my solution. 

Create a measure.

Measure =
IF (
    MAX ( 'Table'[type] ) = "Regular",
    MAX ( 'Table'[hours] ),
    SUMX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[type] = "Extra"
                && 'Table'[date] <= MAX ( 'Table'[date] )
        ),
        'Table'[hours]
    )
)

Get the result.

I attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[JoseLuisAGZZ]

You're a genius!
Thank you very much!

[Aburar_123]

Hellow @JoseLuisAGZZ ,

Could you please provide the result you want to get from the example you explained.

[JoseLuisAGZZ]

Let me explain a lil bit graphic this

Employee    Project     hours      date                   type

     1                A               8          10/02/2022       Regular

     1                A               8          10/03/2022       Regular

     1                B               8          10/03/2022       Extra

     1                C               8          10/04/2022       Extra

     1                A               8          10/04/2022       Regular

     1                A               8          10/05/2022       Extra

Accumulative will be like this:

Employee    Project     hours      date                   type         accumulative

     1                B               8          10/03/2022       Extra         8

     1                C               8          10/04/2022       Extra        16

     1                A               8          10/05/2022       Extra        24

Filtered by project A want to be like this:

Employee    Project     hours      date                   type

     1                A               8          10/02/2022       Regular

     1                A               8          10/03/2022       Regular

     1                A               8          10/04/2022       Regular

     1                A               24        10/05/2022       Extra

Filtered by project C want to be like this:

Employee    Project     hours      date                   type

     1                C               16        10/04/2022       Extra

[JoseLuisAGZZ]

Sure

What im trying to say is that if I apply an ALL(Project)  it will return the 8 hours in the day 3 and what I want is an acumulated by criteria

              Dates      10/02/2022              10/03/2022             10/04/2022

Employee   
1                                  8                               8                            16

in the day 4 is 16 because this employee did an extra on project B on day 3 so the acumulated in project A of criteria "Extra"  will be 16