Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Table Totals Manipulation

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 For...
  • VahidDM's avatar
    VahidDM
    4 years ago

    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's avatar
Jihwan_Kim
Icon for Super User rankSuper User
4 years ago

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's avatar
Anonymous
Not applicable
4 years ago

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.