Forum Discussion
Tracking Totals by Dates
Hey all,
This is a sample of my data. The actualy data has over 15k records and I would love to upload the whole thing here to make it easier but I don't know how.
I am trying to track the prepared date vs the approved date by month (and maybe by year).
For example, from the below sample, in Feb 2024, 4 projects were prepared but 3 were approved.
In Jul 2024, 2 projects were prepared and 2 were approved.
So and so forth.
| Team | Type | Project Description | Prepared Date | Prepared By | Approved Date |
| P | CO | NEW BUILD./SITE. CHURCH 1-1-2-A3-B-SP-AL | 8/23/2024 | PF5 | 8/26/2024 |
| P | CO | CONVERT CLUB TO RESTAURANT 1-1-5-B-B 2003 IBC | 7/31/2024 | ARN | 7/31/2024 |
| S | CO | REMODEL/ADD COFFEE SHOP IN STORE/1-1-2-B-B/SPRINK | 5/5/2025 | ARN | |
| P | CC | NEW 4 STORY S.F. RES W/ ATT GAR (1-4-5-R3-B-13R) 06 IBC | 2/6/2024 | ARN | 2/6/2024 |
| P | CQ | NEW 4 STORY S.F. RES W/ ATT GAR (1-4-5-R3-B-13R) 06 IBC | 2/6/2024 | PF5 | |
| S | CC | NEW 4 STORY S.F. RES W/ ATT GAR (1-4-5-R3-B-13R) 06 IBC | 2/15/2024 | ARN | 2/15/2024 |
| P | CC | NEW 4 STORY S.F. RES W/ ATT GAR (1-4-5-R3-B-13R) 06 IBC | 2/6/2024 | ARN | 2/6/2024 |
| O | CO | HI RISE OFFICE REMODEL 1-12-1-B-A 2006 IBC 100%SPK / FA | 7/31/2024 | ARN | 7/31/2024 |
| O | CQ | NEW 4 STORY S.F. RES W/ ATT GAR (1-4-5-R3-B-13R) 06 IBC | 1/26/2024 | ARN | 1/26/2024 |
In BI, I've created a Date table:
When I go to graph, I jus get straight, horizontal lines:
Any help would be greatly appreciated!
Thanks
8 Replies
1. Create a Date table using CALENDAR(MIN(PreparedDate), MAX(PreparedDate))
2. Create following 2 measures
Count of Prepared =
CALCULATE(
COUNTROWS(Base),
NOT(ISBLANK(Base[PreparedDate])),
USERELATIONSHIP(Base[PreparedDate], 'Date'[Date])
)Count of Approved =
CALCULATE(
COUNTROWS(Base),
NOT(ISBLANK(Base[ApprovedDate])),
USERELATIONSHIP(Base[ApprovedDate], 'Date'[Date])
)3. Buld the visual using year and month from Date table and the mesures from the Base tabel
Thank you for your assistance.
I created the date table:
I am getting the following error:
Hi,
I am not sure if I understood your question correctly, but I tried to create a sample pbix file like below.
Please check the below picture and the attached pbix file.
Thank you for your assistance.
I came very close with your advice, but there is a slight issue.
This is my graph after mirroring your BI file:
The part that's circled should be merged in between the dates instead of it's own standalone number. Does that make sense?
For instance, here are the empty approved dates in my full data:
So for March 2024, the line graph for total approved should dip a little below the total prepared by 1.
For May 2025, the line graph for total approved should dip a little below the total prepared by 13.
For Aug 2024, the line graph for total approved should dip a little below the total prepared by 22.
So on....
Both lines, total approved and total prepared, should not be in perfect unison and that "blank" on the x-axis should not be there.
