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.

PaulDBrown's avatar
PaulDBrown
Community Champion
5 years ago

CM_Mills 

 

Apologies upfront since I see a solution has been offered by v-janeyg-msft while I was working on an alternative.

Allow me to include it anyway, since it might be of benefit for others (even if just as a practice exercise)

First the model

 

The measure to get the last value by ID and month:

 

Month end value = 
VAR EOMDate = //to calculate the last date by month and ID
    MAXX (
        FILTER (
            ALL ( FactTable ),
            FactTable[Unique Identifier] = SELECTEDVALUE ( 'ID Table'[ID] )
                && MONTH ( FactTable[Date - Trade] )
                    = SELECTEDVALUE ( 'Calendar Table'[Month Numb] )
        ),
        FactTable[Date - Trade]
    )
RETURN
    CALCULATE (
        [Sum Open Interests],
        FILTER ( 'Calendar Table', 'Calendar Table'[Cal Date] = EOMDate )
    )

 

and you get this: