Slicing accounting values based on account name

[nwitstine]

I have a simple data set with columns of data, account code, account name, date (month/year), and account activity. I'm trying to slice the data to get just activity values for accounts of a certain name, but because there are multiple results (one result for each month), I'm having trouble setting up a custom measure or column to pull this information. 

[nwitstine · Accepted solution]

Here is what I ended up doing (my table name is 'NOI 2016'):

1. I created a calculated column: Month = 'NOI 2016'[Date].[Month]
2. And a measure that counts the number of months: Months = countx('NOI 2016','NOI 2016'[Month])
3. A measure that calculates the YTD activity: YTD = totalytd(sum('NOI 2016'[Activity]),'NOI 2016'[Date].[Date])
4. A measure that averages the YTD activity of the applicable number of months: YTD Avg = DIVIDE('NOI 2016'[YTD],'NOI 2016'[Months])
5. A measure that annualizes the average for 12 months (I could only figure out how to work this using the divide function, so instead of multiplying my avg by 12, I divide by 1/12 to get the same result): YTD Annualized = DIVIDE('NOI 2016'[YTD Avg],1/12)

The final measure gives me my projected annual amount based on YTD figures. In this case, I end up filtering by account name to isolate just NOI.

[TomMartens]

Hey,

I'm wondering what your expected result is ...

Does this simple approach not work for you:

Create a slicer from the column "account name" and a table visual that contains all the columns except "month/year".

The columns will  automatically be grouped and by default numeric columns will be "summed".

Regards

Tom

[nwitstine]

The simple approach wouldn't work because I would like to retain the month/year column.

Here is some sample data

Sheet 1: this how my data exports

Sheet 2: this is how I modify the data (unpivot to get month/year into a column)

I can easily use this data to produce NOI figures over time, however, I am trying to now get an annualized NOI figure that calculates based on the number of months. For this data set, it's easy enough to add up the 12 months of figures, however, I'd like to create a measure that "knows" how to annualize based on the number of months of data it receives. 

For example, if I have data through May 2018, the measure should add up 5 months, get an average and multiply by 12 for an annualized figure. 

[v-danhe-msft]

Hi @nwitstine,

Based on my test, you can refer to below steps:
1.Create a new measure.
Measure = CALCULATE(SUM(Sheet2[Activity]),FILTER(ALL(Sheet2),'Sheet2'[Date]<=MAX('Sheet2'[Date])&& YEAR('Sheet2'[Date])=YEAR(MAX(Sheet2[Date]))))/MONTH(MAX('Sheet2'[Date]))

2. Create a Slicer visual and add the [Date] field. Create a card visual and add the measure. Now you can see the result controlled by the data slicer.

You can also download the PBIX file to have a view.

https://www.dropbox.com/s/0la4wsxe6sydaad/Slicing%20accounting%20values%20based%20on%20account%20nam...

Regards,

Daniel He

[nwitstine]

Hi Daniel,

I'm really new to Power BI so I'm not exactly sure what your measure is doing, but when I select all months, I would expect my net operating income to equal the total from sheet 1 (17.51M), however, I am getting 4.35M. 

2 things that I would guess are missing:

1. I don't see anything that filters by account name so that the activity included is only for rows where the account name is "Net Operating Income." - I did try adding in an account name slicer for this, but it didn't solve anything.

2. I don't see anything that annualizes the figure (multiplies by 12). It appears to only be taking a monthly average.

[v-danhe-msft]

Hi @nwitstine,

From your description, you said “For example, if I have data through May 2018, the measure should add up 5 months, get an average and multiply by 12 for an annualized figure.” I guess you want to calculate an average value based on the month, right? If you want to slice the values based on the account name, you just need to modify the formula:

Measure = CALCULATE(SUM(Sheet2[Activity]),FILTER(Sheet2,

    'Sheet2'[Date]<=MAX('Sheet2'[Date])&&YEAR(Sheet2[Date])=YEAR(MAX(Sheet2[Date]))&&Sheet2[Account Name]=MAX(Sheet2[Account Name])))

/MONTH(MAX('Sheet2'[Date]))

If you want to see the total value by month, you just need to create a new measure: Total value = SUM(Sheet2[Activity])

Now you can see when you select the all months and the “Net Operating Income”, you could get the total value “17.51M” and the measure shows the average value of the “Net Operating Income”.

Due to I am not sure what value you want to calculate, so I didn’t multiply any values, If I misunderstand you, please just let me know.

You can also download the PBIX file to have a view.

https://www.dropbox.com/s/mzbe7lkmivbx9rp/Slicing%20accounting%20values%20based%20on%20account%20nam...

Regards,

Daniel He

[Anonymous]

Can you post some sample data and your expected outcome ?  If you need fix to your currnet measure, post the formula you used as well.

Thanks

Raj