Summing Issue with Measure

[CM_Mills]

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 day may not be the calendar last day due to no data on weekends, bank holidays, etc.  Also the last day could be different per contract as different exchanges have different weekday & holidays.

I have a measure that works but the measure won't sum so when adding to tables by month this does not work correctly. I believe this is because it is not recogniosing the measure as a number.

Here is an example taken from a snap shot of the 5th June of what I’m trying the achieve:

For code ABC in May the last day would be 30th so in the new column I would require new ‘Open Interest – Lots (Month End)’ column to be 156. Where as in June the latest day would the 5th June so the new ‘Open Interest – Lots (Month End)’ column  would be 158. When this is ran the next day (6th June) the 5th June should then be 0 and the Open Interest from the 6th June should be used.

XYZ shows that the last date could be different per ‘Unique Identifier’ per month.

Table:	Date - Trade Calendar		Data	Data		New Column
Column:	Date - Trade		Unique Identifier	Open Interest - Lots		Open Interest  - Lots (Month End)
	22/05/2020		ABC	156		0
	23/05/2020		ABC	178		0
	24/05/2020
	25/05/2020
	26/05/2020		ABC	145		0
	27/05/2020		ABC	198		0
	28/05/2020		ABC	145		0
	29/05/2020		ABC	189		0
	30/05/2020		ABC	156		156
	31/05/2020
	01/06/2020
	02/06/2020		ABC	145		0
	03/06/2020		ABC	156		0
	04/06/2020		ABC	147		0
	05/06/2020		ABC	158		158
	22/05/2020		XYZ	895		0
	23/05/2020		XYZ	874		0
	24/05/2020		XYZ	815		0
	25/05/2020		XYZ	845		0
	26/05/2020		XYZ	845		0
	27/05/2020
	28/05/2020
	29/05/2020		XYZ	845		0
	30/05/2020		XYZ	849		0
	31/05/2020		XYZ	879		879
	01/06/2020		XYZ	789		0
	02/06/2020		XYZ	875		0
	03/06/2020
	04/06/2020
	05/06/2020		XYZ	899		899

The mesure i am currently using is 

Open Interest – Lots (Month End) = (IF ( MAX ( 'Date - Trade Calendar'[Date - Trade] ) IN VALUES ( Data[Date - Trade] ),

IF ( MAX ( 'Date - Trade Calendar'[Date - Trade] ) = CALCULATE ( LASTNONBLANK ( Data[Date - Trade],

SUM ( Data[Open Interest - Lots] ) ),

FILTER ( ALL ( Data ),

Data[Unique Identifier] = MAX ( Data[Unique Identifier] ) && MONTH ( Data[Date - Trade] ) = MONTH ( MAX ( Data[Date - Trade] ) ) ) ),

CALCULATE ( LASTNONBLANKVALUE ( 'Data'[Date - Trade],

SUM ( Data[Open Interest - Lots] ) ),

FILTER ( ALL ( Data ), Data[Unique Identifier] = MAX ( Data[Unique Identifier] ) && MONTH ( Data[Date - Trade] ) = MONTH ( MAX ( Data[Date - Trade] ) ) ) ),

0 ),

BLANK () ))

This works to bring back the correct number when looking at the daily numbers however the idea is to be able to include it in a monthly sum which isn’t working. I believe the issue is that Power Bi is not considering it a number to add. It brings up a number when the number is the last day of a calendar month however if the number falls on not the last day when summed it brings up a blank response.

I would really be appreciative of any help with this

[PaulDBrown]

@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:

[CM_Mills]

@v-janeyg-msft massive Thank you, this works

[CM_Mills]

@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]

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]

@v-janeyg-msft Thank yo uagain for all your help.

Are you please able to add YEAR into this too?

[CM_Mills]

@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 · Accepted solution]

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.

[v-janeyg-msft]

Hi, @CM_Mills 

It’s my pleasure to answer for you.

According to your description,I think you can change your measure.

Like this:

Measure = 
VAR A =
    CALCULATE (
        MAX ( 'Date - Trade Calendar'[Date - Trade] ),
        FILTER (
            ALL ( Data ),
            Data[Unique Identifier] = MAX ( Data[Unique Identifier] )
                && FORMAT ( Data[Date - Trade], "YYYYMM" )
                    = FORMAT ( MAX ( 'Date - Trade Calendar'[Date - Trade] ), "YYYYMM" )
        )
    )
RETURN
    IF (
        MAX ( 'Date - Trade Calendar'[Date - Trade] )
            IN VALUES ( Data[Date - Trade] )
                && MAX ( Data[Open Interest - Lots] ) <> BLANK (),
        IF (
            SELECTEDVALUE ( Data[Date - Trade] ) = A,
            CALCULATE (
                SUM ( Data[Open Interest - Lots] ),
                FILTER (
                    ALL ( Data ),
                    [Unique Identifier] = SELECTEDVALUE ( Data[Unique Identifier] )
                        && [Date - Trade] = A
                )
            ),
            0
        )
    )

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]

@CM_Mills , I taken wrong names

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

or

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

[CM_Mills]

@amitchandak Thank you.

I can't seem to get them to work. I have added the 'table' data. What should i add the last FORMAT? 'Data' is the table not the column.

Open Interest - Lots (Monthly) 3 = calculate(lastnonblankvalue('Date - Trade Calendar'[Date - Trade], Sum(data[Open Interest - Lots])) , filter(allselected(data), data[Unique Identifier] =max(data[Unique Identifier]) && format('Date - Trade Calendar'[Date - Trade],"YYYYMM") =format(max(data[Data]),"YYYYMM")))

[CM_Mills]

Thank you for lookin at this @amitchandak . Looking at those measure though, they do not take into account the the 'unique Identifer'. Does that matter?

[amitchandak]

@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])))