Creating Table and Adding Column From Existing Table

[Anonymous]

Hi, I'm inserting a new table and got part of it working properly but can't figure out how to get through one more step.  I've used the below DAX to summarize our cases sitting in an open status on any given date:

Open Cases = 
ADDCOLUMNS (
    CALENDAR (date(2021,1,1), max('Case History'[CreatedDate])),
    "Count", CALCULATE (
        DISTINCTCOUNT ( 'Case History'[CaseNumber] ),
        FILTER (
            'Case History',
            'Case History'[CreatedDate] <= [Date]
                && (
                    'Case History'[ClosedDate] > [Date]
                        || ISBLANK ( 'Case History'[ClosedDate] )
                )
        )
    )
)

And this works as expected giving the results below:

However I'd like to get the Count split for each date by another field 'Case History'[Origin] from the same existing Case History table.  I've tried working with SUMMARIZE and some other options but keep getting errors so not sure what I'm doing wrong.  The ideal output I'd like to have is below, if anyone could please help point me in the right direction?

EDIT: Sample table data:

CreatedDate	ClosedDate	CaseNumber	Origin
1/3/2021	1/3/2021	1	Customer Initiated
12/20/2020	1/3/2021	2	Customer Initiated
1/2/2021	1/4/2021	3	Customer Initiated
12/2/2020		4	Internal
12/31/2020	1/2/2021	5	Internal
12/1/2020	12/9/2020	6	Internal
1/2/2021	1/3/2021	7	Customer Initiated
1/1/2021		8	Customer Initiated
1/6/2021		9	Customer Initiated
1/1/2021	1/8/2021	10	Internal

Using this sample, I'd like to see results of the 'open case' table as something like this:

Snapshot Date	Open Count	Origin
1/1/2021	3	Internal
1/1/2021	2	Customer Initiated
1/2/2021	2	Internal
1/2/2021	4	Customer Initiated
1/3/2021	2	Internal
1/3/2021	2	Customer Initiated

[lbendlin]

Instead of 

ADDCOLUMNS (
    CALENDAR (date(2021,1,1), max('Case History'[CreatedDate])),

You would say something like 

ADDCOLUMNS (
    CROSSJOIN(CALENDAR (date(2021,1,1), max('Case History'[CreatedDate])),VALUES('Case History'[Origin])),

and then apply the Origin filter as needed.

[Anonymous]

Cool thank you, that did work to add the Origin column in, separating into different rows for the different Origin values, I just have one more question.  The Counts are showing the monthly totals as the same for each Origin row rather than subdividing the totals, I've tried to do some things with the filter but no luck; do you know how I'd resolve that? 

[lbendlin]

Please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to work with. Please show the expected outcome.

[Anonymous]

Sure, edited my initial message to include a table of sample data and expected results

[v-kkf-msft · Accepted solution]

Hi @Anonymous ，

Please try the following formula:

Open Cases = 
ADDCOLUMNS (
    CROSSJOIN( CALENDAR (date(2021,1,1), max('Case History'[CreatedDate])), DISTINCT('Case History'[Origin]) ),
    "Count", CALCULATE (
        DISTINCTCOUNT ( 'Case History'[CaseNumber] ),
        FILTER (
            'Case History',
            'Case History'[CreatedDate] <= [Date]
                && 'Case History'[Origin] = EARLIER('Case History'[Origin])
                && (
                    'Case History'[ClosedDate] > [Date]
                        || ISBLANK ( 'Case History'[ClosedDate] )
                )
        )
    )
)

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

That completed it, thank you both so much in getting me on the right track!  I really appreciate y'all helping me out.