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


Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Top Solution Authors