Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!!!
Solved! Go to Solution.
Hi,
hope this will help you. Dynamic measure based on your calendar.
Proud to be a Super User!
Hi,
hope this will help you. Dynamic measure based on your calendar.
Proud to be a Super User!
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!
Sure. I needed to change it a little, but this i a result:
Result:
Count Correct Rows - Aggregatable - new one
Count Correct Rows - old one, that works only for day representation
Proud to be a Super User!
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
Hi,
You may download my PBI file from here.
Hope this helps.
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
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?
User | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |