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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Mark_BI
New Member

Power BI - ElapsedDays (Calculate days between dates with measure in separate table)

Hi all,

 

I'm creating a dashboard in Power BI where currently, the data comes from a single Excel file, refreshed completely once a week (though all column headers remain the same).

 

With one table labelled 'Data', I've added a measure being elapsedDays = DATEIFF ( [Interaction_Start_Date],TODAY() , DAY).

This works perfectly in the Date Hierarchy (Year/Quarter/Month/Day) format, as this returns the days from the earliest start date and todays date correct, no matter what they may be/change to.

 

I've attempted to create a measure in a separate table to return the same information, though I break the data in the dashboard by doing so (a table with 12 months as the column headers turns to hundreds of days).

 

As I'm not that great with DAX etc yet, I've searched other various other posts/suggestions and failed so far.

Any help on how to fix this would be much appreciated.

 

Thanks

3 REPLIES 3
Ritaf1983
Super User
Super User

Hi @Mark_BI 

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

My file is quite large, so I've made a written example below.

Note: It's originally listed as text then converted to a table. The bold are the headers.

 

Activity_Start_dateActivity_End_DateActivity_TypeStatus
1/01/20211/01/2021Type 1Closed
1/01/20215/04/2022Type 2Closed
1/06/20221/06/2022Type 3Closed
1/06/20221/06/2022Type 4Closed
1/06/20221/06/2022Type 5Closed
1/06/20231/07/2023Type 5Closed
1/06/2023 Type 4In Progress
1/10/2023 Type 1Unallocated
1/10/20232/10/2023Type 3Closed

 

Once in Power BI, what I did was create the table.

 

Rows - Activity_Type

Columns - Activity_Start_Date

                        Month

Values - Count of Activity_Type

 

It would show as below:

 

Activity TypeJanuaryFebruaryMarchAprilMayJuneJulyAugustSeptemberOctoberNovemberDecemberTotal
Activity 16658547233663249836366561
Activity 206728316999060
Activity 36321639010935084
Activity 414255039795013281294312374
Activity 5459112401894911940265430560
Total13121275134131192168651191301741081639

 

 

I then added the DAX measure in Power BI- 

  1. Fields
  2. Right click the 'Data' table (Data being what I named the table)
  3. Select 'New measure'
  4. Enter DAX expression elapsedDays = DATEIFF ( [Activity_Start_Date],TODAY() , DAY

This then created a useable filter where I could show/filter open and aged activities i.e. any 'Activity_End_Date' where there is no date is an activity that is considered 'Open'.

 

 

The 'Data' table works absolutely perfect with the elapsedDays measure input.

When creating a new 'Measure' table, it can't link up the relationship without causing the issues.

 

Hopefully this clears up what I'm trying to achieve, please let me know if I've missed anything.

 

Is that supposed to be "DATEDIFF" not "DATEIFF" ? Also the DAX looks to be missing an end bracket?

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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