Im having issues with Cummulative Sum with repeated Dates

[Anonymous]

i have a column CountCumulative and it has the value 1 or is blank with dates. the dates are repeated because there can be multiple sales on one day. if a sale has happen the value in CountCumulative should be 1 otherwise it should be blank. Now i want a cumulative sales. like a total of how many sales were on day 1 and then the total sales of day2 and also add day 1 sales into it. i want it date wise.

Table name is Logs_View. 

This is whats showing right now which is obviously not correct. 

below is the formula im using

This is whats showing in the Editor.

[Anonymous]

I used this 

and its still the same

[123abc]

The issue with your current formula for calculating cumulative sales in Power BI DAX likely arises from how it handles duplicate dates. Here's a refined approach that incorporates logic to address repeated dates in your "CountCumulative" column:

1. Define a Measure:

Create a new measure in Power BI and name it Cumulative Sales.

2. DAX Formula:

The issue with your current formula for calculating cumulative sales in Power BI DAX likely arises from how it handles duplicate dates. Here's a refined approach that incorporates logic to address repeated dates in your "CountCumulative" column:

1. Define a Measure:

Create a new measure in Power BI and name it Cumulative Sales.

2. DAX Formula:

Cumulative Sales = 
VAR LatestDate = MAX(Logs_View[Date])
RETURN
    CALCULATE(
        SUM(Logs_View[CountCumulative]),
        FILTER(
            ALL(Logs_View),
            (Logs_View[Date] <= EARLIER(LatestDate)) &&
            (
                // Handle duplicate dates:
                OR(
                    // If not the latest date, include all rows for the date
                    NOT(Logs_View[Date] = LatestDate),
                    // For the latest date, include only rows with CountCumulative = 1
                    Logs_View[Date] = LatestDate && Logs_View[CountCumulative] = 1
                )
            )
        )
    )

Explanation:

1. VAR LatestDate: This variable stores the maximum date value from the "Date" column, ensuring the calculation considers all dates up to that point.
2. CALCULATE: This function evaluates the SUM of "CountCumulative" within a specific filter context.
3. FILTER: This function narrows down the rows used in the calculation based on the following conditions:
   • Logs_View[Date] <= EARLIER(LatestDate): This ensures the calculation considers all dates up to, but not including, the latest date.
   • Handling duplicate dates:
     • NOT(Logs_View[Date] = LatestDate): For dates other than the latest date, all rows are included in the calculation.
     • Logs_View[Date] = LatestDate && Logs_View[CountCumulative] = 1: For the latest date, only rows where "CountCumulative" is 1 are included, effectively considering only the first sale on that day.
4. SUM(Logs_View[CountCumulative]): This calculates the sum of "CountCumulative" for the filtered rows, resulting in the cumulative sales.

This approach addresses the repeated date issue by ensuring that on the latest date, only the first sale (with "CountCumulative" = 1) is considered in the cumulative sum, while all sales are included for other dates.

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

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[Anonymous]

Hello,
Thank you for replying but this keep loading and doesnt run as it says it runs out of memory. Im have 24gb of RAM and yet it says that, Can you use this a little efficiently ?