DAX - Count Days Between two date fields on a Date Axis

[miketangren]

Hello,

I'm trying to use DAX to count every day between two date fields for a large dataset and create a stacked column chart broken out by Program.  You can do this in excel by means of the COUNTIFS function.

I have two tables - a status table & a date table.  I'd like to calculate with DAX the same thing I calculate in Excel with the COUNTIFS function, but broken out by Program on a legend with Date as the Axis.

Example dataset:

(My formula in Column G, COUNTIFS, is =COUNTIFS(B:B,"<="&F4,C:C,">="&F4)

Program Status Table                                                                                   Date Table

Program	Status Start Date	Status End Date			Date	COUNTIFS
A	1/1/2022	2/1/2022			1/1/2022	2
A	1/5/2022	1/20/2022			1/2/2022	2
A	1/10/2022	2/1/2022			1/3/2022	3
A	1/15/2022	1/25/2022			1/4/2022	3
A	1/20/2022	2/1/2022			1/5/2022	5
A	1/25/2022	1/20/2022			1/6/2022	5
B	1/1/2022	2/1/2022			1/7/2022	5
B	1/5/2022	1/25/2022			1/8/2022	6
B	1/10/2022	2/1/2022			1/9/2022	6
B	1/15/2022	1/20/2022			1/10/2022	8
B	1/20/2022	2/1/2022			1/11/2022	8
B	1/25/2022	1/25/2022			1/12/2022	8
C	1/3/2022	2/1/2022			1/13/2022	9
C	1/8/2022	1/20/2022			1/14/2022	9
C	1/13/2022	2/1/2022			1/15/2022	11
C	1/18/2022	1/25/2022			1/16/2022	11
C	1/23/2022	2/1/2022			1/17/2022	11
C	1/28/2022	1/20/2022			1/18/2022	12
					1/19/2022	12
					1/20/2022	14
					1/21/2022	11
					1/22/2022	11
					1/23/2022	12
					1/24/2022	12
					1/25/2022	13
					1/26/2022	9
					1/27/2022	9
					1/28/2022	9
					1/29/2022	9
					1/30/2022	9
					1/31/2022	9
					2/1/2022	9

Thank you for your help!!!

[bolfri · Accepted solution]

Hi,

hope this will help you. Dynamic measure based on your calendar.

Count Correct Rows =

var date_ref = SELECTEDVALUE(dim_calendar[Date])

return

    COUNTX(

        FILTER('Sample';AND('Sample'[Status Start Date] <= date_ref;'Sample'[Status End Date]>=date_ref));

        COUNTROWS('Sample')

    )

You can use Program column as you want. No relationship needed.

[miketangren]

Hi @bolfri ,

This Solution does exactly as expected.  Thank you!

One follow-up question.  I'd like to show date hierarchy on my axis, with date rolling up to Month & Quarter.  PBI will not accept the Month or Quarter axis - visual shows blank when I try that.  Is there a way to rewrite this to account for those?  Can they be included as variables?

Thanks again!

[bolfri]

Sure. I needed to change it a little, but this i a result:

Count Correct Rows - Aggregatable =

var min_date = FIRSTDATE(dim_calendar[Date])

var max_date = LASTDATE(dim_calendar[Date])

return

    COUNTX(

        FILTER('Sample';

            AND(

                OR(

                    OR(

                        //Scenario 1

                        AND('Sample'[Status Start Date]<=min_date;'Sample'[Status End Date]>=max_date);

                        //Scenario 2

                        AND('Sample'[Status Start Date]>=min_date;'Sample'[Status End Date]<=max_date)

                    );

                    OR(

                        //Scenario 3

                        AND('Sample'[Status Start Date]<=min_date;AND('Sample'[Status End Date]>=min_date;'Sample'[Status End Date]<=max_date));

                        //Scenario 4

                        AND(AND('Sample'[Status Start Date]>=min_date;'Sample'[Status Start Date]<=max_date);'Sample'[Status End Date]>=max_date)

                    )

                );

            //Exclude all records with wrong data, when start is after end

            'Sample'[Status Start Date] <= 'Sample'[Status End Date]

            )

        );

        COUNTROWS('Sample')

    )

Graphic representation on each scenario:

Result:
Count Correct Rows - Aggregatable - new one

Count Correct Rows - old one, that works only for day representation

[Bifinity_75]

Hi @miketangren , how many programas have you got?. 
if you have few programs try this:

- Create a diferent calculate column for every program:

COUNTIFS_A = CALCULATE(COUNTROWS('Program Status Table'), 
FILTER(ALL('Program Status Table'),'Program Status Table'[Status Start Date] <='Date Table'[Date] && 'Program Status Table'[Status End Date]>='Date Table'[Date]),'Program Status Table'[Program]="A")

COUNTIFS_B = CALCULATE(COUNTROWS('Program Status Table'),
FILTER(ALL('Program Status Table'),'Program Status Table'[Status Start Date] <='Date Table'[Date] && 'Program Status Table'[Status End Date]>='Date Table'[Date]),'Program Status Table'[Program]="B")

COUNTIFS_C = CALCULATE(COUNTROWS('Program Status Table'),
FILTER(ALL('Program Status Table'),'Program Status Table'[Status Start Date] <='Date Table'[Date] && 'Program Status Table'[Status End Date]>='Date Table'[Date]),'Program Status Table'[Program]="C")

So you can use the programs as a legend for your chart

The result:

Best Regards

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[Bifinity_75]

Hi @miketangren , try this calculate column:

COUNTIFS = CALCULATE(COUNTROWS('Program Status Table'), 
FILTER(ALL('Program Status Table'),'Program Status Table'[Status Start Date] <='Date Table'[Date] && 'Program Status Table'[Status End Date]>='Date Table'[Date]))

The result:

Best Regards

[miketangren]

Hi @Bifinity_75 ,

Using the calculated column worked, but it I am not able to add Program to the legend.  How can I modify the formula to accept the row context for Program?