Measure Total

[Laila92]

I have a measure "x" that calculates a cumulative of sorts. I want the total to show the value of the last month I am showing in a table, instead of a sum of all (because it is a cumulative). I know this normally works with adding an iterator and the values of all months. However, probably because I am using variables, it is not behaving as expected. When I wrap the measure in another measure "y", it does work to add the iterator. I just rather not add unnecessary measures to my data model. See measures below:

Y = 
MAXX( 
    VALUES ( Dates[MonthYear] ),
    CALCULATE (
        ( x
    )
)
)

X = 

VAR todayMonthYearPlusOne =   VALUE (
                    YEAR ( DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) + 1, 1 ) )
                        & FORMAT (
                            MONTH ( DATE ( YEAR ( TODAY () ), MONTH (TODAY () ) + 1, 1 ) ),
                            "0#"
                        ))  

VAR tmpBillingPeriod =
    ADDCOLUMNS (FILTER(Deals_Lookup, Deals_Lookup[Won Time (Clone For Reports)] <> BLANK()),
        
        "MonthYearBegin", IF (
            ISBLANK ( [Won Date] ),
            VALUE ( YEAR ( TODAY () ) & FORMAT ( MONTH ( TODAY () ), "0#" ) ),
            VALUE ( YEAR ( [Won Date] ) & FORMAT ( MONTH ( [Won Date] ), "0#" ) )
        ),
        "MonthYearEnd", IF (
                            ISBLANK ( [Lost Date]),
                            VALUE ( YEAR ( TODAY () ) & FORMAT ( MONTH ( TODAY () ), "0#" ) ),
                                    IF (
                                        DAY ( [Lost Date] ) <> -1,
                                        VALUE ( YEAR ( [Lost Date] ) & FORMAT ( MONTH ( [Lost Date] ), "0#" ) ),
                                        VALUE (
                                            YEAR ( DATE ( YEAR ( [Lost Date] ), MONTH ( [Lost Date] ) - 1, 1 ) )
                                                & FORMAT (
                                                    MONTH ( DATE ( YEAR ( [Lost Date] ), MONTH ( [Lost Date] ) - 1, 1 ) ),
                                                    "0#"
                                                )
                                        )
                                    )
                                )
                            )
    VAR tableWithAllMonthYears =  GENERATE (
                tmpBillingPeriod,
                SUMMARIZE ( Dates, [Year], [MonthNum], [MonthYearNum] )
            )
VAR tableWithOnlyWonMonthYears = FILTER (
            tableWithAllMonthYears,
            [MonthYearNum] >= [MonthYearBegin]
                && [MonthYearNum] < [MonthYearEnd]
        )            

VAR tmpTableDeals =
    SELECTCOLUMNS (
        tableWithOnlyWonMonthYears,
        "DealTitle", [Title],
        "Customerid", [Organization ID],
        "monthyear",Dates[MonthYearNum]
   
    )
VAR tmpTableDeals2 =
    SUMMARIZE(tmpTableDeals,[Customerid],[monthyear],"count",1)
RETURN

VAR custcount = 
    SUMX(tmpTableDeals2,[count])

RETURN

VAR X = MAXX( 
    VALUES ( Dates[MonthYear] ),
    CALCULATE (
        ( custcount)
    )
)


RETURN
MAXX( 
    VALUES ( Dates[MonthYearNum] ),
    CALCULATE (
        ( SUMX(tmpTableDeals2,[count]))
    )
)

Sor for example, I have a table as follows:

MonthYear	Measure X	Measure Y (what i want)
Jan 2020	308	308
Feb 2020	320	320
Mar 2020	330	330
Apr 2020	357	357
May 2020	380	380
June 2020	383	383
Total	2078	383

[v-zhenbw-msft]

Hi @Laila92 ,

How about the result after you follow the suggestions mentioned in my original post?

Could you please provide more details or expected result about it If it doesn't meet your requirement?

If you've fixed the issue on your own please kindly share your solution. If the above posts help, please kindly mark it as a solution to help others find it more quickly.

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-zhenbw-msft]

Hi @Laila92 ,

If you want to display the maximum month measure value in TOTAL, we can create a measure to meet your requirement.

Measure 2 = 
var _maxdate = CALCULATE(MAX('Table'[Year&month]),ALLSELECTED('Table'))
var _x = [Measure]
var _y = CALCULATE([Measure],FILTER('Table','Table'[Year&month]=_maxdate))
return
IF(ISFILTERED('Table'[Year&month]),_x,_y)

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

BTW, pbix as attached.

[Greg_Deckler]

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Also, see this: https://community.powerbi.com/t5/Quick-Measures-Gallery/Matrix-Measure-Total-Triple-Threat-Rock-amp-...

[amitchandak]

@Laila92 ,The information you have provided is not making the problem clear to me. Can you please explain with an example.
Can you share sample data and sample output in table format?
Appreciate your Kudos.