Table Totals Manipulation

[Anonymous]

Hi there

I was wondering if any of you can please assist with the query below.

I have calculated a measure that is giving me correct values for months; however, I would like to amend the DAX Formula so the total shows the calculated value for the last month.

Scenario:

02. Month-Year	FYEO (Incorrect Total)	FYEO (Goal)
Jul-21	29,856,591	29,856,591
Aug-21	30,873,993	30,873,993
Sep-21	30,469,306	30,469,306
Oct-21	30,899,305	30,899,305
Total	144,196,758	30,899,305

Noting that other calculated measures are part of the example below, I would like to use a combination of VAR and SUMMARIZE (table manipulation) to arrive to the goal

FYEO (Incorrect Total) = 
 
VAR MonthstoEOFY = 
    CALCULATE( MEDIAN ( DATES[02. FY Periods Remaining] ) ,
        KEEPFILTERS ( VALUES ( 'DATES'[Date] ) ) )

VAR MonthlyFYEO = 
    [YTD Actual] + ( [YTD Actual Monthly Average] * MonthstoEOFY ) 

VAR TotalFYEO = SUMMARIZE ( DATES , DATES[02. Offset - CurMonth] , "Month Total" , MonthlyFYEO )

RETURN
IF( HASONEVALUE ( DATES[02. Offset - CurMonth] ) , 
    MonthlyFYEO, 
        SUMX ( TotalFYEO, [Month Total] ) )

I believe I need to change the last part of the formula SUMX ( TotalFYEO, [Month Total] ) so it references the "Month Total" for the last month but unsure of which formula work best.

I feel I'm getting close 😅

Any ideas would be greatly appreciated.

Cheers

[VahidDM · Accepted solution]

Hi @Anonymous 

Try this:

FYEO (Incorrect Total) =
VAR MonthstoEOFY =
    CALCULATE (
        MEDIAN ( DATES[02. FY Periods Remaining] ),
        KEEPFILTERS ( VALUES ( 'DATES'[Date] ) )
    )
VAR MonthlyFYEO = [YTD Actual] + ( [YTD Actual Monthly Average] * MonthstoEOFY )
VAR TotalFYEO =
    SUMMARIZE ( DATES, DATES[02. Offset - CurMonth], "Month Total", MonthlyFYEO )
RETURN
    IF (
        HASONEVALUE ( DATES[02. Offset - CurMonth] ),
        MonthlyFYEO,
        CALCULATE (
            MonthlyFYEO,
            FILTER ( ALL ( DATES ), DATES[02. Offset - CurMonth] = "Oct-21" )
        )
    )

If the values in DATES[02. Offset - CurMonth] column are in Date format then you can change the last row of the above code to:

FILTER ( ALL ( DATES ), DATES[02. Offset - CurMonth] = MAX(DATES[02. Offset - CurMonth] ))

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[Jihwan_Kim]

Hi,

I am not sure if I understood your question correctly, but I tried to simplify it like below.

Please check the below picture and the attached pbix file.

Result: =
VAR monthlymeasure =
SUM ( Data[FYEO] )
VAR totalmeasure =
CALCULATE ( SUM ( Data[FYEO] ), Data[Month-Year] = "Oct-21" )
RETURN
IF ( HASONEVALUE ( Data[Month-Year] ), monthlymeasure, totalmeasure )

[Anonymous]

Thank you Jihwan, but not quite.

The FYEO is not a column with data but a calculated measure in itself.

I'm confident the monthly values are correct and I would like to keep the formula as it is but adjusting the total to show the value of the last month.

Thank you for your help.