cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dmadeira
Regular Visitor

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

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:

 

dmadeira_0-1597949929157.png

 

 

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

1 ACCEPTED SOLUTION
mwegener
Super User
Super User

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.

 

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener work at KUMAVISION AG , one of the world's largest
implementation partners for Microsoft Dynamics. #
"Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website


View solution in original post

3 REPLIES 3
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
pranit828
Super User
Super User

HI @dmadeira 

Can you please post a sample output 




PBI_SuperUser_Rank@1x.png


Hope it resolves your issue? 
Did I answer your question? Mark my post as a solution!

Appreciate your Kudos, Press the thumbs up button!!
Linkedin Profile
mwegener
Super User
Super User

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.

 

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener work at KUMAVISION AG , one of the world's largest
implementation partners for Microsoft Dynamics. #
"Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website


View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.