Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Measure Total

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 XMeasure Y (what i want)
Jan 2020308308
Feb 2020320320
Mar 2020330330
Apr 2020357357
May 2020380380
June 2020383383
Total2078383

 

4 Replies

  • Anonymous ,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.

  • v-zhenbw-msft's avatar
    v-zhenbw-msft
    Icon for Community Support rankCommunity Support

    Hi Anonymous ,

     

    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.

  • v-zhenbw-msft's avatar
    v-zhenbw-msft
    Icon for Community Support rankCommunity Support

    Hi Anonymous ,

     

    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.