Cumulative Using Summarized Table

[HugoJesus]

Hello everyone, 

First of all, I'll explain what I'm trying to do. 
1. Create a Summarized Table with the following columns "Calendar", "Created Tickets", "Closed Tickets", "Daily Open Tickets" (Is the difference between "Created Tickets" - "Closed Tickets") and finnaly "Open Tickets" (Cumulative of Daily Open Tickets).

2. Then to create cumulative, I'm using the before summarized table to do that, but is only returning the same value "39".

There's an example:

The DAX that I'm using:

Total_Open_Tickets =

var TempTable =

SUMMARIZE

(

Date_Link,

Date_Link[Calendar],

"Created_Tickets",CALCULATE(count(Date_Link[id]),FILTER(Date_Link,Date_Link[Date_Type] = "Creation")),

"Closed_Tickets",CALCULATE(count(Date_Link[id]),FILTER(Date_Link,Date_Link[Date_Type] = "Closure")),

"Daily_Open_Tickets",

CALCULATE(count(Date_Link[id]),FILTER(Date_Link,Date_Link[Date_Type] = "Creation"))

-

CALCULATE(count(Date_Link[id]),FILTER(Date_Link,Date_Link[Date_Type] = "Closure"))

)

Var LastDay = MAX ( Date_Link[Calendar] )

var TempTable2 =

ADDCOLUMNS

(

TempTable,

"Open_Tickets",

CALCULATE

(

SUMX(TempTable,[Daily_Open_Tickets]),

Date_Link[Calendar] <= LastDay

)

)

return

TempTable2

Best Regard's

Hugo Jesus

[amitchandak]

@HugoJesus , In your script of addcolumns .

"Open_Tickets",
CALCULATE
(
SUMX(Filter(TempTable,Date_Link[Calendar] <= earlier(Date_Link[Calendar])),[Daily_Open_Tickets])
)

new column =SUMX(Filter(Total_Open_Tickets ,[Calendar] <= earlier([Calendar])),[Daily_Open_Tickets])

[HugoJesus]

Sorry, is not working. Give to me the follow error.

[v-easonf-msft · Accepted solution]

Hi,  @HugoJesus 

Try to modify the formula as below:

...
.....
VAR LastDay =
    MAX ( Date_Link[Calendar] )
VAR TempTable2 =
    ADDCOLUMNS (
        TempTable,
        "Open_Tickets",
        var _date = [Calendar]
        return
            SUMX(
                FILTER(
                    TempTable,
                    [Calendar]<=_date
                ),
                [Daily_Open_Tickets]
            )
    )
RETURN
    TempTable2

The result will show as below:

Please check the attached pbix file for more details.

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

[HugoJesus]

Hi @v-easonf-msft,

It is possible to create a Variable, with this code and create x-axis visualizations by date?

Regard's

Hugo Jesus 

[HugoJesus]

Sorry the correct term is "Measure" instead of "Variable".

[HugoJesus]

Hi @v-easonf-msft ,

This is a different level and my idea is to have the "Open_Tickets" in Area Chart by date.

The code that I'm using is the same that you have sent before, but a little different at the end.

Create a Measure:

Total_Open_Tickets =

var TempTable =

SUMMARIZE

(

Date_Link,

Date_Link[Calendar],

"Created_Tickets",CALCULATE(count(Date_Link[id]),FILTER(Date_Link,Date_Link[Date_Type] = "Creation")),

"Closed_Tickets",CALCULATE(count(Date_Link[id]),FILTER(Date_Link,Date_Link[Date_Type] = "Closure")),

"Daily_Open_Tickets",

CALCULATE(count(Date_Link[id]),FILTER(Date_Link,Date_Link[Date_Type] = "Creation"))

-

CALCULATE(count(Date_Link[id]),FILTER(Date_Link,Date_Link[Date_Type] = "Closure"))

)

VAR TempTable2 =

ADDCOLUMNS (

TempTable,

"Open_Tickets",

var _date = Date_Link[Calendar]

return

SUMX(

FILTER(

TempTable,

Date_Link[Calendar]<=_date

),

[Daily_Open_Tickets]

)

)

return

SUMX(TempTable2,[Open_Tickets])

The result of this. .. is only showing the "Daily_Open_Tickets" instead of "Open_Tickets".
At below, as you can see the "Open_Tickets" is the correct value and "Total_Open_Tickets" is the Measure that I've asked for help before, both are different.
The "Total_Open_Tickets" is showing the "Daily_Open_Tickets" instead of cumulative.

Any idea how to solve this.

Regard's

Hugo Jesus

[HugoJesus]

Anyone have an idea about this?

Help ...

Regards

Hugo Jesus

[HugoJesus]

Hi @v-easonf-msft , 

That's a miracle, I searched in many sites, but no one have mentioned something like this.

It works perfectly, this is why I love to share my doubts here in this forum.

Thanks a lot. 

Regard's

Hugo Jesus

[darentengmfs]

Hi @HugoJesus 

Please take a look at the following thread: https://community.powerbi.com/t5/Desktop/Cumulative-Total/td-p/43115

[HugoJesus]

Hi Darentengmfs,

I've already saw that, but is not the same example, because I'm using SUMMARIZE instead of the primary table.

Regards

Hugo Jesus