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.

CM_Mills's avatar
CM_Mills
Helper II
5 years ago

v-janeyg-msft 

 

Hi Janey,

 

Thank you for taking a look. This measure again works when looking at the daily numbers.

 

However, when looking at this on a month view it doesn't work. It is just brings back 0.

 

Do yo uhave any ideas why this isn't being treated as a number and summing?

 

Thanks

 

Chris

v-janeyg-msft's avatar
v-janeyg-msft
Community Support
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.