Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
bhmiller89
Helper V
Helper V

Creating date based measures

I have a table called "HelpDeskIncidents" that includes columns such as "OpenDate" "ClosedDate" and "IncidentNumber"

 

I want to be able to show, in one visual, the number of tickets opened AND closed within a certain date period. So if I have a graph it would show that on January 2, 2018 there were, let's say, 36 tickets opened and 28 closed. 

 

I'm not sure how to write these measures any help is greatly appreciated. 

 

Oh, I have a date table and I have the "OpenDate" as the key with the date table if that helps 

1 ACCEPTED SOLUTION

Here's a simplified version (attached). Just need 2 date tables.

 

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

3 REPLIES 3
Greg_Deckler
Super User
Super User

Hmm, here is one approach.

 

Create 3 date tables. Date table 1 (Calendar1) relate to OpenDate in your table. Create a column in this table that does a count of RELATED or RELATEDTABLE. Date table 2 (Calendar2) relate to ClosedDate in your table. Create the same column in this table. Date table 3 (Calendar3) relate to Calendar1 and Calender2. Use Calender3 in your Axis and the values from calculated columns in your other 2 date tables.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

OK, a bit different than I originally planned, but check this out (attached).

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Here's a simplified version (attached). Just need 2 date tables.

 

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.