Showing countrows with zero rows for a selected date range

[ValeriaBreve]

Hello,

I have a table called Date_Table_Action which has a 1->* relationship to table BBP_Actions on the date.

I need to display a table that has all the continous dates between the min date on BBP_Actions and the max date on BBP_Actions and the related number of actions (setup is 1 row per action in BBP_Actions).

If there are no actions on that day, it should show zero.

I don't seem to be able to get there... this is my measure:

# Count of Actions =

VAR _FilteredDateTable=

CALCULATETABLE(Date_Table_Action,

FILTER(Date_Table_Action,Date_Table_Action[Date]<=MAX(BBP_Actions[ActionCompleteTargetDate])),

FILTER(Date_Table_Action,Date_Table_Action[Date]>=MIN(BBP_Actions[ActionCompleteTargetDate])))

RETURN

CALCULATE(

    COUNT(BBP_Actions[Id]),_FilteredDateTable)

In my table, I have all date related fields coming from Date_Table_Action, and the only added field is # Count of Actions.

I only get this in return: either only the dates that have actions (normal), or when I set the "Show Items with no data" to true, then I get the whole range of dates from Date_Table_Actions which is far too wide - it should only be the range as per filter in the measure.

Can you please help me understand why this happens and how to modify the  measure to show 1 row per date in the range I wish?

Thanks a lot in advance!

Kind regards

Valeria

[Anonymous]

Hi @ValeriaBreve ,

Based on the description, try using the following DAX formula.

Count of Actions =
VAR _MinDate = MIN(BBP_Actions[ActionCompleteTargetDate])
VAR _MaxDate = MAX(BBP_Actions[ActionCompleteTargetDate])
VAR _DateRange = 
    FILTER(
        Date_Table_Action,
        Date_Table_Action[Date] >= _MinDate &&
        Date_Table_Action[Date] <= _MaxDate
    )
RETURN
SUMX(
    _DateRange,
    CALCULATE(
        COUNT(BBP_Actions[Id]),
        BBP_Actions[ActionCompleteTargetDate] = Date_Table_Action[Date]
    )
)

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[SachinNandanwar]

Can you please post some sample data ?

[ValeriaBreve]

@SachinNandanwar I don't think I can attach anything unfortunately. For the Date_Table_Actions it would be a simple date table:

For the BBP_Actions table, here is some sample data from the table. Relationship is 1 --> * from Date_Table_Action[Date] to BBP_Actions[ActionCompleteTargetDate]

Id	Status	ActionCompleteTargetDate
67	Validated	12-Sep-24
68	New	18-Sep-24
69	Study Ongoing	26-Sep-24
70	New	12-Sep-24
71	New	19-Sep-24
72	New	12-Sep-24
77	Completed	17-Sep-24
78	New	17-Oct-24
79	New	26-Sep-24
80	New	25-Sep-24
81	New	26-Sep-24
82	New	24-Sep-24
83	New	26-Sep-24
84	New	26-Sep-24
85	New	26-Sep-24
86	New	30-Sep-24
87	New	30-Sep-24
88	New	01-Oct-24
89	New	01-Oct-24
90	New	10-Oct-24
91	New	17-Oct-24
92	New	17-Oct-24
93	New
94	New	30-Oct-24
98	New
99	New
100	New
101	New
102	New

Thanks!