creating a summary table

[cobus_19]

Hi

So I have a date set with a structure like this:

origin date | client | client id | xxx | xxx | status 1 date | status 2 date | etc

Each row is a unique client and contains a unique client id

My problem is essentially this, I want to filter the data monthly to see how many clients went through each status.

My problem is that the status's are sometimes seperated by as much as a week.

Thus filtering "origin date" to October, for example, I won't see if any of the clients with "origin date" prior to october enter any new statuses in October.

I was thinking I could create a summary table of the structure:
date | status 1 count | status 2 count | status 3 count | etc

But i'm having a hard time creating this using PowerQuery. If this was excel I would just use COUNTIF("date"="status x date"), but I can't find a similar functionality for PowerQuery, I would very much appreciate some help.

Regards
Cobus

[v-shex-msft · Accepted solution]

Hi @cobus_19,

You can use summarize function(DAX) to achieve your requirement.

Table = SUMMARIZE(Sheet1,Sheet1[origin date],"State1",COUNTX(FILTER(ALL(Sheet1),Sheet1[status 1 date]=EARLIER(Sheet1[origin date])),[status 1 date]),"State2",COUNTX(FILTER(ALL(Sheet1),Sheet1[status 1 date]=EARLIER(Sheet1[origin date])),[status 2 date]),"State3",COUNTX(FILTER(ALL(Sheet1),Sheet1[status 1 date]=EARLIER(Sheet1[origin date])),[status 3 date]))

If above is not help, please provide some sample data.

Regards,

Xiaoxin Sheng

[cobus_19]

This was perfect! Thank you very much!

[cobus_19]

If I may introduce a further complexity:
This gives me a great daily summary, however how would I set about including some other columns from my main table?

For example, what if I want to see a breakdown of [category] per day. How do I introduce this without breaking the daily counts?

So my current table output is

Origin Date | Status 1 Count | Status 2 Count | etc

date1 | 10 | 20
date2 | 5| 30

etc

My desired output is:
Origin Date | Category | Status 1 Count | Status 2 Count
date1 | category1 | 5 | 15
date1 | category2 | 5 | 5

date2 | category1 | 1 | 10

date2 | category2 | 2 | 10
date2 | category3 | 3 | 10

Your help is much appreciated.

Regards
Cobus

[v-shex-msft]

Hi @cobus_19,

Yes, you can add other columns from main table.

Table = SUMMARIZE(Sheet1,Sheet1[origin date], //add these columns here

Reference:

SUMMARIZE Function (DAX)

Regards,

Xiaoxin Sheng

[cobus_19]

Hi @v-shex-msft

I have tried that, the problem however is that the summary per item then changes.
I have illustrated my problem in a new post

Regards

Cobus