Forum Discussion

HugoJesus's avatar
HugoJesus
Helper IV
5 years ago
Solved

Cumulative Using Summarized Table

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
Need Help
  • v-easonf-msft's avatar
    v-easonf-msft
    5 years ago

    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.

     

10 Replies

    • HugoJesus's avatar
      HugoJesus
      Helper IV
      5 years ago

      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

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    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's avatar
      HugoJesus
      Helper IV
      5 years ago

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

       

       

      • v-easonf-msft's avatar
        v-easonf-msft
        Community Support
        5 years ago

        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.