Cumulative values - Sum of -> Count of with Strings

[fredsp]

Hi,

I have a data set of issues reported in a project in Jira and I'm extracting Created Date and each Key (a unique number for each issues reported). I plot Count of Keys with Created Date to get a graph on how many issues are created each day, however now my problem.

I would also like to plot the cumulative value for each Created Date but since Key-value is a string I run into problem with my DAX-function.

Anyone out there who knows how to do a SUM of Count of per Created Date so that I can plot the cumulative values instead?

Br,

Fredrik

[fredsp]

Once again, thanks a lot!!

[fredsp]

Thanks a lot @cosm for your support. I'll try this out 😊

[cosm]

Nice - let me now if you run into trouble.

Please mark as solution if the solution solves your problem.

Kudos also appreciated.

[cosm · Accepted solution]

Hi @fredsp 
Here is a solution for your problem using example date. 
Please mark as accepted if it solves your problem 🙂

You want plot cumulative count (of distinct distinct keys) pr date.

Here is a solution for computing cumulative distinct counts pr date:

0) Generate sample data

Jira = 
DATATABLE (
    "Key", STRING, 
    "Created DateTime", DATETIME,
    {
        { "1", "2022-01-01 00:00:00" },
        { "2", "2022-01-01 12:34:56" },
        { "3", "2022-01-01 23:59:59" },
        { "4", "2022-01-02 07:00:00" },
        { "5", "2022-01-02 14:30:45" },
        { "6", "2022-01-03 20:15:30" },
        { "7", "2022-01-03 11:11:11" },
        { "8", "2022-01-04 18:45:22" },
        { "9", "2022-01-05 02:22:33" },
        { "10", "2022-01-6 15:55:44" }
    }
)

Add column Created Date with date part of DateTime column:

Created Date = DATE(
    YEAR(Jira[Created DateTime]),
    MONTH(Jira[Created DateTime]),
    DAY(Jira[Created DateTime])
)

See snippet below:

1) Create a summarized table with count pr date:
In Power BI Model Pane, select "New Table" and add the following DAX statement:

Count pr created date = SUMMARIZE(
Jira,
Jira[Created Date],
"Distinct keys", DISTINCTCOUNT(Jira[Key])
)

 
2) Add cumulative count as ameasure:

Cumulative Count = 
CALCULATE (
    SUM ( 'Count pr created date'[DISTINCT KEYS]),
    FILTER ( ALL ( 'Count pr created date' ), 
    'Count pr created date'[Created date] <= MAX
     ( 'Count pr created date'[Created date] ) )
)

( Filter statement includes all row less than or equal to maximum created date in current filter context)

Done 😀

Now you can use your measure to plot cumulative count as shown below:

Kind regards!