Tracking Totals by Dates

[e175429]

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

[techies]

Hi @e175429 please try this

Prepared Count =

COUNTROWS(Projects)

Approved Count =

CALCULATE (

    COUNTROWS(Projects),

    USERELATIONSHIP(Projects[Approved Date], 'date'[Date]),

    NOT(ISBLANK(Projects[Approved Date]))

)

[e175429]

Are you able to upload your pbix file?

This is what I did:

This is what I got:

[techies · Accepted solution]

Hi @e175429 not able to upload file, there is active relationship between prepared date and date, 

inactive between approved date and date right?

[e175429]

Thank you!

I had it backwards:

My active relationship was between approved date and date and my inactive relationship was between prepared date and date. Once I switched them, I got the correct visual:

Thank you sooooo much!

[Jihwan_Kim]

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.

[e175429]

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.

[SreeniBattula]

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

[e175429]

Thank you for your assistance.

I created the date table:

I am getting the following error: