Forum Discussion

HugoJesus's avatar
HugoJesus
Icon for Helper IV rankHelper IV
6 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" (...
Need Help
  • v-easonf-msft's avatar
    v-easonf-msft
    6 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.

     

HugoJesus's avatar
HugoJesus
Icon for Helper IV rankHelper IV
6 years ago

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

 

 

v-easonf-msft's avatar
v-easonf-msft
Icon for Community Support rankCommunity Support
6 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.

 

  • HugoJesus's avatar
    HugoJesus
    Icon for Helper IV rankHelper IV
    6 years ago

    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

  • HugoJesus's avatar
    HugoJesus
    Icon for Helper IV rankHelper IV
    6 years ago

    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's avatar
      HugoJesus
      Icon for Helper IV rankHelper IV
      6 years ago

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

      • HugoJesus's avatar
        HugoJesus
        Icon for Helper IV rankHelper IV
        6 years ago

        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