Forum Discussion

CM_Mills's avatar
CM_Mills
Helper II
5 years ago
Solved

Summing Issue with Measure

Hi,   I have a measure that the end aim is to create a new column that selects either, the last day of month value per  a unique identifier, or the last number if the current month.   The last da...
Need Help
  • v-janeyg-msft's avatar
    v-janeyg-msft
    5 years ago

    Hi, CM_Mills 

     

    Because the calculation logic is based on date instead of month.

    You can try to add a month column in your date table ,then create a summraize table to diaplay the desired result.

    like this:

    month = MONTH('Date - Trade Calendar'[Date - Trade])

Table = 
SUMMARIZE (
    Data,
    'Date - Trade Calendar'[month],
    Data[Unique Identifier],
    "open",
        VAR a =
            MAXX (
                FILTER (
                    Data,
                    MONTH ( [Date - Trade] ) = SELECTEDVALUE ( 'Date - Trade Calendar'[month] )
                        && [Unique Identifier] = SELECTEDVALUE ( Data[Unique Identifier] )
                        && [Open Interest - Lots] <> BLANK ()
                ),
                [Date - Trade]
            )
        RETURN
            SUMX ( FILTER ( Data, [Date - Trade] = a ), [Open Interest - Lots] )
)

Measure 2 = IF(SELECTEDVALUE('Table'[Unique Identifier])=BLANK(),0,1)

    Here is my sample .pbix file.Hope it helps.

    If it doesn’t solve your problem, please feel free to ask me.

     

    Best Regards

    Janey Guo

     

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

amitchandak's avatar
amitchandak
Super User
5 years ago

CM_Mills , Try a new measure like

 


calculate(lastnonblankvalue(Table[Date - Trade], Sum(Table[Open Interest - Lots])) , filter(allselected(Table), Table[Data] =max(Table[Data]) && format(Table[Date - Trade],"YYYYMM") =format(max(Table[Data]),"YYYYMM")))

 

or

 

calculate( Sum(Table[Open Interest - Lots]) , filter(allselected(Table), Table[Data] =max(Table[Data]) && format(Table[Date - Trade],"YYYYMM") =format(max(Table[Data]),"YYYYMM") && [Date - Trade] =max(Table[Data])))