Wrong Totals when calculating MIN and MAX Measures

[RobGer]

Dear Power BI Users,

I have a table for all the projects of the company, in this table we have the following relevant columns:

Project ID - self-explanatory.
Projectcategory - There are 8 Categories corresponding to a deparment inside the company.
ProjectBeginDate - The real Begin Date of the project.
ProjectEndDate - The real End Date of the project.

We evaluate every semester, how many months of work are left for each project till the end of the year.

I tried to calculate it with the following measure, but it doesn't aggregate correctly if I insert the ProjectCategory column in the matrix visualization:

Months pro Year and Semester = 

VAR SelectedYearMonthEnd = CALCULATE(VALUE(CONCATENATE(SELECTEDVALUE('Calendar'[Jahr]),IF(SELECTEDVALUE('Calendar'[Semester]) = 1, "06", "12")))) -- This results in either YYYY06 or YYYY12 (for example for 2023: 202306 or 202312)
VAR SelectedYearMonthBegin = CALCULATE(VALUE(CONCATENATE(SELECTEDVALUE('Calendar'[Jahr]),"01"))) -- This results in (for example): 202301
VAR EndeIst = MINX('ProjectList',VALUE(FORMAT('ProjectList'[ProjectEndDate],"YYYYMM"))) -- Project End Date in the same format to be able to compare
VAR BeginnIst = MAXX('ProjectList',VALUE(FORMAT('ProjectList'[ProjectBeginDate],"YYYYMM"))) -- Same as above for Project Begin Date.

VAR MinEnd = MIN(SelectedYearMonthEnd, EndeIst) -- Minimum between Project End Date and the selected Date, so that we correctly calculate the number of months left.
VAR MaxBeginn = MAX(SelectedYearMonthBegin, BeginnIst) -- Maximum between Project Begin Date and the selected Date, so that we correctly calculate the number of months left.

RETURN

MAX(0,(MinEnd - MaxBeginn) + 1)

The measure works correctly for each row (each row representing a project). The moment I insert the measure in a table or matrix, and try to aggregate by category, the measure displays only 0, I assume the totals are being calculated incorrectly.

I tried also using a SUMX Measure separately, but it displays the same results.

RETURN
SUMX(
    VALUES('Projektliste SGr'[Bereich]),
    MAX(0, MIN(SelectedYearMonth, MinValue) - MAX(SelectedYearMonth, MaxValue) + 1)
)

The Relationships are as follows:

Intended Result:

The Measure should, when displayed on a matrix visual, show the number of months left from the point of evaluation (either january or july) till either the end of the project, or the end of the selected year (whichever comes first).

This is a middle step to calculate the number of hours left for each project. When I created another similar measure to calculate the number of hours per project, I got the same results as with this measure. The totals were wrong.

The matrix will be actively filtered by two slicers, one for the year, and one for the semester.

It seems like the answer is right in front of me, but I can't see it. Any solutions?

Thanks in advance,
Robert

[some_bih]

Hi @RobGer Robert 🙂 I tried to connect your 2 measures. Still, it is not possible as in 2nd there is SelectedYearMonth or MinValue which is  not mentioned in 1st measure.

To understand your issue, it will be the best to share data (pbi and underlying file) with sample as expected output.

It could be that "totals are wrong" but it depends how viewer will "see" results.

I notices your picture that date table is ONLY inactive relationship. Please note that to use DAX with inactive relationship you use use USERELATIONSHIP with CALCULATE to leverage this "inactive" relationship.

[RobGer]

Hello @some_bih !

I uploaded the data and pbix file to wetransfer (I had to summarize because of confidentiality and size).

On the first page of the document, you will see the first measures I created, this are not really relevant to the issue at hand, but explain why I have inactive relationships between the main table and the date table.

Now to the issue I described: on the second page you can see two matrixes, one with the current result (each project's number of months is calculated correctly individually, but the subtotal per category doesn't display the correct value, dynamically adapted to the year and semester).

The expected result (Table on the right, with values for 2023) show the number of months per project and the subtotal for each category.

The second measure I wrote down was another attempt at the same thing, but I got the same results.

I have read that using a combination of SUMX and SUMMARIZE, this could be achieved, but I couln't translate it properly to my data model.

Link to the Documents: https://we.tl/t-lepp6EiH7P

Thanks again!

[some_bih]

Hi @RobGer I will take a look and let you know what I can do.

[some_bih · Accepted solution]

Hi @RobGer Plesase create New measure as following. You should get figures as expected.

Did I answer your question? Mark my post as a solution! Kudos Appreciated!

Monate im Jahr und Halbjahr_v2 =

SUMX('Projektliste SGr',

    [Monate im Jahr und Halbjahr])

[RobGer]

Thanks a lot @some_bih ! This had me stuck on several fronts but now I can continue :-)!