counts of status' based on dates

[vips]

I am relatively new to Power BI.

I have a table of orders as shown here. I would like a *single* chart that represents bar graph of counts with date on X-axis.

1. Total new orders = 6
2. Orders by date 
   1. 4/1 = 3
   2. 4/2 = 2
   3. 4/3 = 1
3. Warehouse process date
   1. 4/1 = 1
   2. 4/2 = 2
   3. 4/3 = 2
   4. 4/4 = 1
4. Ship date
   1. 4/2 = 1
   2. 4/4 = 3
   3. 4/5 = 1
   4. 4/10 = 1

order ID	order date	warehouse process date	ship date
1	4/1	4/1	4/2
2	4/1	4/2	4/4
3	4/1	4/3	4/4
4	4/2	4/2	4/10
5	4/2	4/3	4/4
6	4/3	4/4	4/5

What attribute should I use in the X-Axis and how do I get counts of each of the states on specific days?

Thanks much in advance!!

[vivran22 · Accepted solution]

Hello @vips ,

This you can achieve through making a separate calendar table:

dtCalendar = 
ADDCOLUMNS(
    CALENDARAUTO(),
    "Year", YEAR([Date]),
    "Month", EOMONTH([Date],-1)+1
)

then creating relationship between the Date of calendar table and all three dates from your fact table (one active and two inactive):

Add three measures:

Count by Order Date = 
   COUNTROWS(dtTable)


Count by Process Date = 
CALCULATE(
    [Count by Order Date],
    USERELATIONSHIP(dtCalendar[Date],dtTable[warehouse process date])
)


Count by Ship Date = 
CALCULATE(
    [Count by Order Date],
    USERELATIONSHIP(dtCalendar[Date],dtTable[ship date])
)

Select Date from the calendar table and add these meausres in the visual:

You may find the solution pbix file here

Cheers!
Vivek

If it helps, please mark it as a solution
Kudos would be a cherry on the top 🙂

https://www.vivran.in/

Connect on LinkedIn

[amitchandak · Accepted solution]

@vips 

Create a common Date Calendar table. Join all three dates with it. There will one active and and two inactive relations. use userelation to activate joins.

example

Ordered = CALCULATE(COUNT(Table[Order Id ]),USERELATIONSHIP(Table[Order Date],'Date'[Date]) )

warehouse = CALCULATE(COUNT(Table[Order Id ]),USERELATIONSHIP(Table[warehouse process Date],'Date'[Date]) )
Ship = CALCULATE(COUNT(Table[Order Id ]),USERELATIONSHIP(Table[Ship Date],'Date'[Date]) )

Refer my blog how to use : https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[vips]

Thanks!

[vivran22 · Accepted solution]

Hello @vips ,

This you can achieve through making a separate calendar table:

dtCalendar = 
ADDCOLUMNS(
    CALENDARAUTO(),
    "Year", YEAR([Date]),
    "Month", EOMONTH([Date],-1)+1
)

then creating relationship between the Date of calendar table and all three dates from your fact table (one active and two inactive):

Add three measures:

Count by Order Date = 
   COUNTROWS(dtTable)


Count by Process Date = 
CALCULATE(
    [Count by Order Date],
    USERELATIONSHIP(dtCalendar[Date],dtTable[warehouse process date])
)


Count by Ship Date = 
CALCULATE(
    [Count by Order Date],
    USERELATIONSHIP(dtCalendar[Date],dtTable[ship date])
)

Select Date from the calendar table and add these meausres in the visual:

You may find the solution pbix file here

Cheers!
Vivek

If it helps, please mark it as a solution
Kudos would be a cherry on the top 🙂

https://www.vivran.in/

Connect on LinkedIn

[vips]

Thanks!