Check How Many Processes are in a Status at a certain day

[dmadeira]

Hello everyone,

I need some help with an issue!

I have a table in which I have a list of process codes (on column "Informação", and associated to each code, there is a Start_Date and an End_Date (which is equivalent to the start date from the next row, since the column "Attribute" is a status of the process. Like such:

What I need is to know for each day of the year, how many processes I had in each kind of "Attribute".

Ps: I already have a calendar table as well

Can anyone please help me out?

Thanks in advance.

Kind regards,

Duarte

[v-shex-msft]

Hi @dmadeira,

You can use crossjoin with filters to create a expand table with detailed records with category and dates in the range which defied by two date fields.

Spread revenue across period based on start and end date, slice and dase this using different dates 
Regards,

Xiaoxin Sheng

[pranit828]

HI @dmadeira 

Can you please post a sample output 

[mwegener · Accepted solution]

Hi @dmadeira ,

You need to transform the table into transaction data.
For the start date you create an row with +1
For the end date, you create an row with -1

You can use the running total to determine the total.

If you provide the sample data as CSV etc. I can create a sample PBIX.