topic Re: Tracking Totals by Dates in DAX Commands and Tips

Tracking Totals by Dates

e175429 — Mon, 09 Jun 2025 18:38:06 GMT

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

Re: Tracking Totals by Dates

SreeniBattula — Tue, 10 Jun 2025 00:00:00 GMT

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

Re: Tracking Totals by Dates

Jihwan_Kim — Tue, 10 Jun 2025 03:19:11 GMT

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.

Re: Tracking Totals by Dates

e175429 — Tue, 10 Jun 2025 14:50:53 GMT

Thank you for your assistance.

I created the date table:

I am getting the following error:

Re: Tracking Totals by Dates

e175429 — Tue, 10 Jun 2025 16:20:21 GMT

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.

Re: Tracking Totals by Dates

techies — Tue, 10 Jun 2025 17:05:21 GMT

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]))

)

Re: Tracking Totals by Dates

e175429 — Tue, 10 Jun 2025 17:34:53 GMT

Are you able to upload your pbix file?

This is what I did:

This is what I got:

Re: Tracking Totals by Dates

techies — Wed, 11 Jun 2025 06:46:17 GMT

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

inactive between approved date and date right?

Re: Tracking Totals by Dates

e175429 — Thu, 12 Jun 2025 16:29:51 GMT

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!