topic Re: Calculate a measure that has a single value for multiple rows in a table in DAX Commands and Tips

Calculate a measure that has a single value for multiple rows in a table

dbongers — Fri, 01 Jan 2021 21:48:08 GMT

I am working with stock data in a table that includes a ticker column, a date column and a End of Day (EOD) stock value column. I have added an integer column with with a counter for trading dates.

I want to calculate for each ticker what the EOD value is at the start of a selection range, so I can use that number to calculate the growth of the the stock value over time as a percentage of the value at that start date.

My intermediate measures look like this:

EOD Average =average(DALL_CURRENT[EOD])

Ticker in context =FIRSTNONBLANK(DALL_CURRENT[Ticker],DALL_CURRENT[Ticker])

First trading day =min(DALL_CURRENT[Trading day])

First trading day in selection=calculate([First trading day],allselected(DALL_CURRENT))

This works great. The identifier for the first trading day in my selection is assigned to each date in my selection.

Now I want to use this "First trading day in selection" measure to select the EOD value for each ticker at the start date.

My first attempt looks like this:

=calculate([EOD Average],Filter(all(DALL_CURRENT[Trading day]),DALL_CURRENT[Trading day]=[First trading day in selection]))

What is good here is that I get the right values for multiple Tickers

The problem here is that I only get a result for the first trading day and not for all the other dates.

My second attempt to avoid the filter context issue was to build an interim table

=VAR TMP =
FILTER (
SELECTCOLUMNS (
ALL ( 'DALL_CURRENT'),
"TMP[TD]", DALL_CURRENT[Trading day],
"TMP[EOD]", DALL_CURRENT[EOD],
"TMP[Ticker]",DALL_CURRENT[Ticker] ),
TMP[TD]=[First trading day in selection] && TMP[Ticker]=[Ticker in context]
)
VAR EOD_at_Start = averagex(TMP,TMP[EOD])

RETURN EOD_at_Start

I got exactly the same result.

My questions are:

1: why is it that I only see an answer for the first trading date? I kind of understand it for the first calculation, but not for the second.

2: how can I get the right answer for all dates within my selection?

Re: Calculate a measure that has a single value for multiple rows in a table

MattAllington — Sat, 02 Jan 2021 21:18:03 GMT

Time intelligence should not be attempted with a single flat table. You should get a calendar table for this problem. https://exceleratorbi.com.au/power-pivot-calendar-tables/

Join the calendar table to the date column of the data table

switch all references to date from your data table to the calendar table, including in the visuals/slicers

Once you make these changes, your first attempt should work.

the reason it doesn't work with a single table is complex. It has to do with the way the source data is compressed and stored in the database. Removing a filter from a subset of columns, eg ALL(table[column]) or ALLSELECTED(table) will not behave as you expect because of the binding of rows of data together caused by the storage. This row binding does not occur when you use a dimension table

Re: Calculate a measure that has a single value for multiple rows in a table

amitchandak — Mon, 04 Jan 2021 05:19:09 GMT

dbongers ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.