Get Date Counts Based on a Parameter in PowerBI if Data is in one table

Question

I'm stuck in this peculiar issue. I want to get Counts/Visualization of Different Columns which all have dates based on a Parameter but the data is in a single table.

Here is an example -

Data -

Let's consider this as a parameter/Filter that'll filter the data this should be independent of the data so we can give it a range of Min and Max of dates of all columns A, B, and C and it should have all the Months in Series -

So when I select March it should return the counts of March as and all the visualization should also only show March counts-

I'm confused about what will I use as the X-Axis in Visualization so it remains independent of the columns.

EDIT -

Unique ID	Entry Date	Division	Region	OrderDate	Delivery Date
1	01-02-2023	West	WB	02-Sep-23
2	2/30/2023 02:55	West	DEL	02-Sep-23	Oct-23
3	2/30/2023 02:56	East	HAR		Sep-23
4	2/30/2023 02:57	Central	WB	02-Sep-23
5	2/30/2023 02:58	Central	DEL
6	2/30/2023 02:59	East	HAR	02-Sep-23
7	2/30/2023 02:60	East	WB
8	2/30/2023 02:61	Central	DEL	03-Oct-23
9	2/30/2023 02:62	East	HAR
10	2/30/2023 02:63	East	WB	21-Oct-23
11	2/30/2023 02:64	Central	DEL
12	2/30/2023 02:65	Central	HAR	21-May-23
13	2/30/2023 02:66	Central	WB	21-May-23
14	2/30/2023 02:67	Central	DEL	21-May-23	Aug-23
15	2/30/2023 02:68	Central	HAR	21-May-23	Aug-23
16	2/30/2023 02:69	Central	WB	21-May-23
17	2/30/2023 02:70	East	DEL	21-May-23
18	2/30/2023 02:71	East	HAR	21-May-23	Aug-23
19	2/30/2023 02:72	Central	WB	21-May-23	Aug-23
20	2/30/2023 02:73	East	DEL	21-May-23

Expectation -

All the column chart using the above dates should give count based on the month selected in the parameter. I've filter for division and Region applied as well they should also filter the count.

Answer 1

What you need is a Date Table. You can create an example of one with these steps:

1. Create a Calculated Table (via New Table under the Modeling tab in the ribbon) with the following:

DateTable = 
  ADDCOLUMNS(
    CALENDARAUTO(),
    "Month", FORMAT([Date], "MMM"),
    "MonthNum", MONTH([Date]),
    "Year", YEAR([Date]),
    "Year-Month", FORMAT([Date], "yyyy-MM")
  )

2. Once created, click on the Month column in the DateTable, then Sort by column in the ribbon, and select MonthNum. This will ensure Jan, Feb etc... will be ordered correctly.
3. Right-click on DateTable and select Mark as date table and in the pop-up select Date as the Date column.

That's your date table created. Next we need to add relationships to your data table.

But first, your Entry Date column is Date Time data type, so we need a Date version of this:

• Create a new column in your data table with:

Date = DATEVALUE(FORMAT([Entry Date], "yyyy-MM-dd"))

Now to add the relationships. Switch to Model view (third button/icon down on the far-left vertical toolbar). You should see your two tables.

• Click'n'drag the Date from your data table to the Date in the DateTable.
• Do the same for OrderDate and Delivery Date. You should have one active, and two inactive relationships, looking like:

Now you can create Measures based on the above:

ID Count = DISTINCTCOUNT(YourDataTable[Unique ID])

ID Count by EntryDate = [ID Count]

ID Count by OrderDate = CALCULATE([ID Count], USERELATIONSHIP(DateTable[Date], YourDataTable[OrderDate])

ID Count by DeliveryDate = CALCULATE([ID Count], USERELATIONSHIP(DateTable[Date], YourDataTable[Delivery Date])

Finally, for your chart:

• Add DateTable[Month] to the X-axis
• Add ID Count by EntryDate, ID Count by OrderDate, and ID Count by DeliveryDate to the Y-axis

---

Additional

To have interactive charts that update cards with the relevant counts, then you'll need the following:

1. Create a new Calculated Table to your model with the following:

Date Role = DATATABLE("Role", STRING, { {"Entry"}, {"Order"}, {"Delivery"} } )

2. Create a new Measure with the following:

Date Role Count = 
  var eRole = CONTAINS('Date Role', 'Date Role'[Role], "Entry")
  var oRole = CONTAINS('Date Role', 'Date Role'[Role], "Order")
  var dRole = CONTAINS('Date Role', 'Date Role'[Role], "Delivery")

  var eValues =
    CALCULATETABLE(
      DISTINCT(YourDataTable[Unique ID]),
      NOT ISBLANK(YourDataTable[Unique ID]) = eRole
    )

  var oValues =
    CALCULATETABLE(
      DISTINCT(YourDataTable[Unique ID]),
      USERELATIONSHIP(DateTable[Date], YourDataTable[OrderDate]),
      NOT ISBLANK(YourDataTable[Unique ID]) = oRole
    )
  
  var dValues =
    CALCULATETABLE(
      DISTINCT(Sheet2[Unique ID]),
      USERELATIONSHIP(DateTable[Date], YourDataTable[Delivery Date]),
      NOT ISBLANK(YourDataTable[Unique ID]) = dRole
    )
  return
    COUNTROWS( DISTINCT( UNION( eValues, oValues, dValues) ) )

3. Update your charts to use [Date Role Count] in Values or Axis, and use 'Date Role'[Role] for the Legend. Optionally, you can then filter for which "Roles" you want displayed in your chart (eg Order & Delivery).
4. For your card visual, use [Date Role Count] as well.