topic Re: DAX – Accumulated days between two datekeys in DAX Commands and Tips

DAX – Accumulated days between two datekeys

NoccoLoco — Tue, 28 Feb 2023 13:38:25 GMT

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.

Re: DAX – Accumulated days between two datekeys

lbendlin — Mon, 06 Mar 2023 02:42:53 GMT

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?

Re: DAX – Accumulated days between two datekeys

NoccoLoco — Mon, 13 Mar 2023 14:59:52 GMT

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.