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 TableProgramStatus Start DateStatus End Date DateCOUNTIFSA1/1/20222/1/2022 1/1/20222A1/5/20221/20/2022 1/2/20222A1/10/20222/1/2022 1/3/20223A1/15/20221/25/2022 1/4/20223A1/20/20222/1/2022 1/5/20225A1/25/20221/20/2022 1/6/20225B1/1/20222/1/2022 1/7/20225B1/5/20221/25/2022 1/8/20226B1/10/20222/1/2022 1/9/20226B1/15/20221/20/2022 1/10/20228B1/20/20222/1/2022 1/11/20228B1/25/20221/25/2022 1/12/20228C1/3/20222/1/2022 1/13/20229C1/8/20221/20/2022 1/14/20229C1/13/20222/1/2022 1/15/202211C1/18/20221/25/2022 1/16/202211C1/23/20222/1/2022 1/17/202211C1/28/20221/20/2022 1/18/202212 1/19/202212 1/20/202214 1/21/202211 1/22/202211 1/23/202212 1/24/202212 1/25/202213 1/26/20229 1/27/20229 1/28/20229 1/29/20229 1/30/20229 1/31/20229 2/1/20229 Thank you for your help!!!

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.

Anonymous

Not applicable

3 years ago

Solved

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

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!!!

Need Help

bolfri
3 years ago
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.

7 Replies

Bifinity_75
Solution Sage
3 years ago
Hi Anonymous , 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
- Anonymous
  Not applicable
  3 years ago
  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?
bolfri
Solution Sage
3 years ago
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.
- Anonymous
  Not applicable
  3 years ago
  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
    Solution Sage
    3 years ago
    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
Ashish_Mathur
Super User
3 years ago
Hi,

You may download my PBI file from here.

Hope this helps.

Bifinity_75

Solution Sage

3 years ago

Hi Anonymous , 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

Forum Discussion

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

7 Replies

Related content

Counting nulls

Field Parameter appear as count

Counting blank fields based on another column

Counting Blank Fields on a Column that's Counting Values

Count Of Drivers