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
Anonymous
Not applicable

DAX Support: Current Count Based on Time

Hi All!

 

I am looking to create a visual that shows all the current tickets throughout time. My data source is constantly repeating data because of change of ownership and change in month. Basically, the table generates new rows every first day of the month, and I want to keep this because the owners are changing, and I want to acknowledge that. There is a similar formula I was trying to adapt, but it unfortunately doesn't take into account of the changing ownership and duplicate data (understandably). Does anyone here have an idea to how I can adapt the Current Employees formula shown in the link to take into account of duplicate data? I'm afraid I am not skilled enough to understand the DAX formula below yet.

 

Excel link: https://onedrive.live.com/?id=889A7EFB9759AA88%21111&cid=889A7EFB9759AA88

 

 

Current Employees = CALCULATE(COUNTx(FILTER(Employee,Employee[Start Date]<=max('Date'[Date]) && (ISBLANK(Employee[End Date]) || Employee[End Date]>max('Date'[Date]))),(Employee[Employee Id ])),CROSSFILTER(Employee[Start Date],'Date'[Date],None))

 

 

TicketOwnerCreate DateClosed DateDate
123Andrew6/1/2019 6/1/2020
123Tom6/1/2019 7/1/2020
123Christine6/1/20197/19/20208/1/2020
123Christine6/1/20197/19/20209/1/2020
234Lily7/1/2019 6/1/2020
234Bob7/1/2019 7/1/2020
234Nathan7/1/2019 8/1/2020
234Nathan7/1/20198/18/20209/1/2020
345Michael7/1/2020 7/1/2020
345Michele7/1/2020 8/1/2020
345Tim7/1/2020 9/1/2020
456Rebeca6/1/2020 6/1/2020
456Rebeca6/1/2020 7/1/2020
456Vincent6/1/20207/15/20208/1/2020
456Vincent6/1/20207/15/20209/1/2020
567Jason9/1/2020 9/1/2020
678Cassie9/1/2020 9/1/2020
789Patricia8/1/2020 8/1/2020
789Patrick8/1/2020 9/1/2020
890Kevin7/1/2020 7/1/2020
890Tim7/1/20207/10/20208/1/2020
890Andrew7/1/20207/10/20209/1/2020
1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@Anonymous , You can actually create a new table from the above table like this

summzarize(Table, Table[Ticket],Table[Owner], "Create Date", firstnonblank(Table[Create Date],blank()),"Closed Date", lastnonblank(Table[Closed Date],blank()))

 

and used the blog formula

View solution in original post

5 REPLIES 5
amitchandak
Super User
Super User

@Anonymous , You can actually create a new table from the above table like this

summzarize(Table, Table[Ticket],Table[Owner], "Create Date", firstnonblank(Table[Create Date],blank()),"Closed Date", lastnonblank(Table[Closed Date],blank()))

 

and used the blog formula

Anonymous
Not applicable

Hi again @amitchandak ! Thanks again for all your help!

 

I adapted your summary formula to my actual table but am getting null Create and Closed Dates. Can you help me out? I adapted it exactly.

Hi @Anonymous ,

 

Would you please explain more about your expected output? In your sample data, there are many duplicate rows like

123Christine6/1/20197/19/2020 
123Christine6/1/20197/19/2020 

Do they need to be count twice? 

 

Thanks for your understanding and support.

 

Best Regards,

Dedmon Dai

Anonymous
Not applicable

Hi @v-deddai1-msft! Thanks for your help! The repeating data is due to the table refreshing monthly and updating the status in the table, no matter what the table will refresh. I'd want the most recent data as long everything stays the same, but if the data stays the same other than the date of the rfresh date and the director changing.

Hi,

Based on the Data that you have shared in the original post, please show the exact result that you are expecting.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.