# Dax calculation - check if between to dates

Hi all,

Im struggling to figure out how to solve this problem.
I have a table that shows the amount of Work-years (WY) employees are working on various projects, and the start and end date for the various projects (see table below)

Table1

Employee ProjectID WY Start_date End_date
A 1 0,5 01.01.2024 31.08.2024
A 2 0,5 01.01.2024 31.08.2024
A 1 1 01.09.2024 31.12.2024
B 1 1 01.01.2024 31.01.2024
B 2 1 01.02.2024 31.03.2024
B 3 1 01.04.2024 31.12.2024
C 4 0,5 01.01.2024 31.12.2024

I am trying to make a matrix that shows the amount of WY for the various projects per month like this:

Prjojects jan.24 feb.24 mar.24 apr.24 may.24
1 1,5 0,5 0,5 0,5 0,5
2 0,5 1,5 1,5 0,5 0,5
3 0 0 0 1 1
4 0,5 0,5 0,5 0,5 0,5

I have created a date-table and have created a relationship between the two tables but I cant figure out which function to use in my dax calclation to get i to work. My guess is that I need to use a create a formula that checks whether or not the context is between the start and end-date in table 1, but I cant figure out how to go about it.

I appreciate any suggestions.

Cheers.

can you attach the pbix ?
what is the relatinship between your datetable and table1?
if it is on the start date then you will have only months 1 2 4 9

your table1 is a dimension table what I’m missing is the fact table with the data like
employee, project ID , month or date , and WY
and then the relationship will work correctly

kind regards

Roger

H
Hello,

the screenshot shows the result for the project over the months

I created a sample set of data
fact table
dimension table
calendar
added a calculated column the get the correct WY from the dimension table

next created the visual as matrix
attached the pbix file and the XLSX file ( with the dimension table and the fact table )

projects.xlsx (14.0 KB)

project employee and month.pbix (108.3 KB)

kind regards
Roger

Hi Roger,

Sorry for the late reply. I have been away on holiday.
Thank you the response.
In response to your first post:

1. There is a one-to-many relationship between the datetable and start date.
2. I thought table 1 was considered a fact table as this is where all the WY is stored. I have separate dimension tables for projects and employees.

In the pbix you attached I see that you created one row per month for each employee for each project. Maybe I should have stated that inn my orginal post, but what I posted was a sample of my datasett. In my actual dataset I have 500 employees and 200 projects and goes from 2018 up until 2024 (and will continue),. Thus I think this approach, will make the datamodel very large and perhaps slow?

I have tried something like this, but not sure If I get the correct results:

``````WY per mnd =
SUMX (
SUMMARIZECOLUMNS ( DateDim[Year], DateDim[Month] ),
CALCULATE (
VAR EndDate =
MAX ( 'DateDim'[Date] )
VAR StartDate =
MIN ( 'DateDim'[Date] )
VAR RESULT =
CALCULATE (
SUM('Table 1'[WY]),
REMOVEFILTERS ( 'DateDim' ),
'Table1'[Start_date] <= EndDate,
'Table1'[End_date] > StartDate
|| ISBLANK ( 'Table1'[End_date] )
)
RETURN
RESULT
)
)
``````

Do you think something like this can work?

Cheers

Magnus,
I tested you measure and it gives a result but not the table you want.
It is the relation between the datetable and the start date of your Table1 that results in only 4 months ( the months in your start column).

I look further, maybe the user relationship() can help.
Or unpivot your factable so you have only on date column.

to be continued
kind regards,

Roger

project employee and month new.pbix (148.1 KB)

Magnus

I found a workaround ,

started with reference the dimension table called it " Dimensiontable expanded "
added some additional column using M language (–> check via Edit query to see the M code)

column Dates for projects expands the table for each project with dates between startDate and endDate
a correction column for the WY value on a daily basis
a relationship between the datetable and the " Dimensiontable expanded "

below a screenshot of the " Dimensiontable expanded "
you can delete the start Date and End Date via Edit Query, I left them for checking purpose

the row totals on the second visual are not correct and therefore hidden.

the totals on the first visual are correct but miss a day on the last day of the project ( see december and august )

kind regards
Roger

Hi Roger,

Thanks alot! This is perfect!
Thanks for working this out for me.

Cheers!

MAgnus

the matrix is now complete and works according the dates in the dimension table

change or add the measure in the screen capture " Total WY days "

kind regards
roger