Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have two dates
PK StartDate EndDate
1 1/1/2000 1/2/2001
2 1/3/2000 1/2/2002
3 3/2/2001 1/1/2002
4 3/30/2001 3/1/2003
5 4/2/2001 3/1/2003
CountStartDate= CountA([StartDate])
CountEndDate= CountA([EndDate])
Expected Result
CountStartDate CountEndDate
2000 2 0
2001 3 1
2002 0 2
2003 0 3
Actual Result
CountStartDate CountEndDate
2000 2 2
2001 3 3
2002 0 0
2003 0 0
I do not understand how this happens. CountA is counting dates that do not occur in Column CountEndDate.
It returns the dates in CountStartDate column.
Thank you
Robert
Solved! Go to Solution.
Hi @dasrotrad,
You could create a calculated table with the formula below to get your desired output.
Table = ADDCOLUMNS ( DISTINCT ( UNION ( SELECTCOLUMNS ( Table1, "Year", RIGHT ( Table1[StartDate], 4 ) ), SELECTCOLUMNS ( Table1, "Year", RIGHT ( Table1[EndDate], 4 ) ) ) ), "count start", CALCULATE ( COUNT ( Table1[StartDate] ), RIGHT ( Table1[StartDate], 4 ) = EARLIER ( [Year] ) ) + 0, "count end", CALCULATE ( COUNT ( Table1[EndDate] ), RIGHT ( Table1[EndDate], 4 ) = EARLIER ( [Year] ) ) + 0 )
Here is the output.
Hope this can help you!
Best Regards,
Cherry
Hi @dasrotrad,
If it is convenient, could you share the screenshots of your error when you create the calculated table with the formula I provided in the first reply?
In addition, you could have a reference of my test file which has been attached below.
Best Regards,
Cherry
Hi @dasrotrad,
You could create a calculated table with the formula below to get your desired output.
Table = ADDCOLUMNS ( DISTINCT ( UNION ( SELECTCOLUMNS ( Table1, "Year", RIGHT ( Table1[StartDate], 4 ) ), SELECTCOLUMNS ( Table1, "Year", RIGHT ( Table1[EndDate], 4 ) ) ) ), "count start", CALCULATE ( COUNT ( Table1[StartDate] ), RIGHT ( Table1[StartDate], 4 ) = EARLIER ( [Year] ) ) + 0, "count end", CALCULATE ( COUNT ( Table1[EndDate] ), RIGHT ( Table1[EndDate], 4 ) = EARLIER ( [Year] ) ) + 0 )
Here is the output.
Hope this can help you!
Best Regards,
Cherry
Thank you Cherry. When I type the Table formula, the code accepts Table1[StartDate], but when I get to the first instance of Table1[EndDate], the Table1[EndDate] gets the red underline and intelisense quits. Everything subseqeunt becomes undelined with the squigley red line.
Robert
Hi @dasrotrad,
If it is convenient, could you share the screenshots of your error when you create the calculated table with the formula I provided in the first reply?
In addition, you could have a reference of my test file which has been attached below.
Best Regards,
Cherry
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |