Dax Measure works perfect except for row total

[dcormiernj]

I have the following measure that works perfectly except the row totals are not totalling.

The measure is pasted below.  It finds a start date and an end date based on create date field.  then it populates 20% of the value in each of the 5 months.  I pasted an example table below that shows the results I am getting.

FinalAmount =

    SUMX (

      FILTER( 'Project Table',

      EDATE('Project Table'[Create Date],1) <= MAX('calendar'[Date]) &&

      EDATE('Project Table'[Create Date],5) >= MIN('Calendar'[Date])

      ),

      'Project Table'[Value]*.012

        )

The table below is perfect.  each value is correct and the column totals are correct.  but the row total seem to be a copy of the column.  it is not totalling each month for a job total.

any ideas how to get this to total correctly?

thank you

	April	May	Jun	Jul	Aug	Total	Total Should Be
job1	400	400	400	400	400	400	2000
job2	500	500	500	500	500	500	2500
job3	200	200	200	200	200	200	1000
Total	1100	1100	1100	1100	1100	1100	5500

[BeaBF · Accepted solution]

@dcormiernj Hi! try with:

FinalAmountCorrected =
VAR StartDate = EDATE('Project Table'[Create Date], 1)
VAR EndDate = EDATE('Project Table'[Create Date], 5)
VAR MonthsInScope =
CALCULATETABLE (
VALUES ( 'Calendar'[Date] ),
'Calendar'[Date] >= StartDate && 'Calendar'[Date] <= EndDate
)
VAR NumMonths = COUNTROWS ( MonthsInScope )
RETURN
SUMX (
FILTER (
'Project Table',
EDATE('Project Table'[Create Date], 1) <= MAX ( 'Calendar'[Date] )
&& EDATE('Project Table'[Create Date], 5) >= MIN ( 'Calendar'[Date] )
),
'Project Table'[Value] / 5
)

BBF

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

👍 Kudos are appreciated

🔥 Proud to be a Super User!

[BeaBF · Accepted solution]

@dcormiernj Hi! try with:

FinalAmountCorrected =
VAR StartDate = EDATE('Project Table'[Create Date], 1)
VAR EndDate = EDATE('Project Table'[Create Date], 5)
VAR MonthsInScope =
CALCULATETABLE (
VALUES ( 'Calendar'[Date] ),
'Calendar'[Date] >= StartDate && 'Calendar'[Date] <= EndDate
)
VAR NumMonths = COUNTROWS ( MonthsInScope )
RETURN
SUMX (
FILTER (
'Project Table',
EDATE('Project Table'[Create Date], 1) <= MAX ( 'Calendar'[Date] )
&& EDATE('Project Table'[Create Date], 5) >= MIN ( 'Calendar'[Date] )
),
'Project Table'[Value] / 5
)

BBF

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

👍 Kudos are appreciated

🔥 Proud to be a Super User!

[dcormiernj]

Thank you BeaBF yes this looks like it will work and I will try it.  But just before I saw your response,  I decided to try adding another measure that does a hasonefilter on the month and then do sum on the original measure.  And this worked.  I now get the row totals.  But I like yours too.  Thank you very much.

[BeaBF]

@dcormiernj Well done! Please try my measure and if it works, accept the answer as solution. At the same time, please paste your working formula, so we can help other users with the same issue!

BBF

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

👍 Kudos are appreciated

🔥 Proud to be a Super User!

[dcormiernj · Accepted solution]

Thank you BBF,  and yes I will try yours to make sure it works.  pasted below are my final measures that do work.  Thank you for all of your help.

FinalAmount Part 1 =

    SUMX (

      FILTER( 'Project Table',

      EDATE('Project Table'[Create Date],1) <= MAX('calendar'[Date]) &&

      EDATE('Project Table'[Create Date],5) >= MIN('Calendar'[Date])

      ),

      'Project Table'[Value] *.012

        )

FinalAmount FINAL =  If(HASONEFILTER('Calendar'[Month]),[FinalAmount Part1], SUMX(VALUES('Calendar'[Month]),[FinalAmount Part 1]))