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

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
NoccoLoco
Frequent Visitor

DAX – Accumulated days between two datekeys

I’ve got a Tabular data model that contains information about projects.

 

The project table “FaktProjects” include “StartDate”, “EndDate”, “ProjectName”, “Organisation” and some ID’s to connect to other Dimension tables.

 

My “DimDate” table have some general date values and are connected to the “FaktProject” table through FaktProjects[StartDate] * <- 1 DimDate[DateKey]

 

Both “StartDate” and “EndDate” are in the Datekey format (“YYYYDDMM”) and where a project is ongoing (They don’t have a EndDate) the “EndDate” values is set to “99999999”.

 

I want to calculate the days between “StartDate” and “EndDate” and the sum needs to be visualized in a matrix where the value should accumulated over selected Year and shown for each month.

 

Example: Project 1 has “StartDate” 20220101 and “EndDate” 20220205.

In the matrix the value for January should be 31 since the project has been active throughout January.

In February the value for the same project should be 36 since the project were still going for 5 days in February.

In Mars, the value should still be 36 since the project is no longer active and no more days should be added.

2 REPLIES 2
lbendlin
Super User
Super User

They don’t have a EndDate) the “EndDate” values is set to “99999999”.

Make sure you don't use CALENDARAUTO or auto date/time hierarchies. This will result in ginormous date tables.  Better to replace with null or TODAY().

 

Are you connecting live to the tabular model, or with mixed mode?

Thank you for your replay, yes im connected to the tabular model live . And as for the date, its an old soulution, and the way they set it up is so that date key is 99999999. Its to big of a soulution to start rebuilding alot of logic using today() so i just have to go with the flow. 


I managed to solve this problem using SQL View that i added to my Model.
With a crossjoin i had to specify all the dates that a project contains wich resulted in a table with 821 rows became a 160.000 rows view.

Not the best soulution but it works for now.

Thank you for the response.

 

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

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