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 

 

Thank you. I'm new to power Bi and DAX so please forgive my misunderstanding.

 

I have created the month column within the 'Date - Trade' table.

 

I have then created the new summerize table.  Should i join make an 'active relationship' to the data table via 'unique identifier'. When i have tried this the link ins't working right, it just bring back every identifer.

 

Where does 'measure 2' go? Does this get created in the new 'table'? i have tired this but not really understanding how it works.

 

Thanks again for your help

v-janeyg-msft's avatar
v-janeyg-msft
Community Support
5 years ago

Hi, CM_Mills 

 

The function of measure2 is to control not to display the blank() value,It is placed in the filter pane of the visual.

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 Thank yo uagain for all your help.

     

    Are you please able to add YEAR into this too?