Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
marcio_fornari
Resolver I
Resolver I

Cumulative Sum with Duplicate Date

Hello, I have a problem to sum a Cumulative Measure. 

Maybe this is happening because there duplicate Dates? 

 

If don't have duplicate dates: It's OK

cumulate ok.png

But, if have a "RAZAO SOCIAL" value in the table, some date is duplicate, it's ok, but the Cumulative sum It's not ok.

 

E4Ghhzt

The Dax is: 

 

 

 

 

Acumulado Saldo = 
CALCULATE(
    [Saldo do Dia];
    FILTER(
        All(fDADOS); fDADOS[DATA VENCIMENTO] <= MAX(fDADOS[DATA VENCIMENTO])
    )
)

 

 

 

 

 

Can someone help me?

Thanks

1 ACCEPTED SOLUTION

Hi @marcio_fornari ,

 

Sorry for our mistake in the formula, we can use the following measure to meet your requirement:

 

Acumulado Saldo =
CALCULATE (
    [Saldo do Dia];
    FILTER (
        ALL ( fDADOS );
        fDADOS[DATA VENCIMENTO] < MAX ( fDADOS[DATA VENCIMENTO] )
            || (
                fDADOS[DATA VENCIMENTO] = MAX ( fDADOS[DATA VENCIMENTO] )
                    && fDADOS[RAZÃO SOCIAL] <= MAX ( fDADOS[RAZÃO SOCIAL] )
            )
    )
)

 

7.jpg

 


Best regards,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
Anonymous
Not applicable

Hi, I am facing a similar issue and the above solution has not worked for me. 
I have data on sales of around 1600 restaurants for the months January-May 2022. Whatever I have tried it just duplicates the selected months' value. I want to make a card that shows Year to date value so e.g if I select April from the slicer so I want the card to show the sum of sales values of that particular restaurant from January-April while excluding months after April. 

v-lid-msft
Community Support
Community Support

Hi @marcio_fornari ,

 

We can try to use the following measure to meet your requirement:

 

Acumulado Saldo = 
CALCULATE(
    [Saldo do Dia];
    FILTER(
        All(fDADOS); fDADOS[DATA VENCIMENTO] <= MAX(fDADOS[DATA VENCIMENTO]) && fDADOS[RAZÃO SOCIAL] <= MAX(fDADOS[RAZÃO SOCIAL])
    )
)

 

5.jpg

 

 

If it doesn't meet your requirement, Could you please show the exact expected result based on the Tables that you have shared?


Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hello, @v-lid-msft  Thanks for all. It's Almost

 

The Expected Result is:

 

cumulative.png

 

Hi @marcio_fornari ,

 

Sorry for our mistake in the formula, we can use the following measure to meet your requirement:

 

Acumulado Saldo =
CALCULATE (
    [Saldo do Dia];
    FILTER (
        ALL ( fDADOS );
        fDADOS[DATA VENCIMENTO] < MAX ( fDADOS[DATA VENCIMENTO] )
            || (
                fDADOS[DATA VENCIMENTO] = MAX ( fDADOS[DATA VENCIMENTO] )
                    && fDADOS[RAZÃO SOCIAL] <= MAX ( fDADOS[RAZÃO SOCIAL] )
            )
    )
)

 

7.jpg

 


Best regards,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hello @v-lid-msft 

Perfect! Thanks for All.

amitchandak
Super User
Super User

Your cumulative is for dates and does not include RAZAO SOCIAL, that is why you are getting that

 

Refer :https://community.powerbi.com/t5/Desktop/ALLEXCEPT-in-a-multitable-setup/td-p/403205

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @

Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on Linkedin

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.