cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper II

## Reference a measure in another measure

Hello,

Is it possible to reference a measure in another measure? If yes, then can you please provide an example?

Thank you,

M.R.

1 ACCEPTED SOLUTION
Community Champion

I just realized I made a mistake in my code. That code for Last Entry will return the date of the last entry, not the last entry itself. Looking back at your original formula I think it should be

```Last Entry = CALCULATE(
LASTNONBLANK( 'Trends'[Total Unresolved], 1),
DATESMTD(DateTable[Date])
)```

...or whatever the column is with the number you're trying to tally. Not the timestamp column.

Now, if you really really want to have that last date with an entry, add a third measure:

```Last Date = CALCULATE(
MAX('Santiago Trends'[action_timestamp]),
DATESMTD(DateTable[Date])
)```

You could use my original mistake version of Last Entry, but this version also works, and is slightly easier to type.

Proud to be a Super User!

29 REPLIES 29
MVP

@MR2001 Yes. This is done a lot when calculating percentages. There are numerous examples, but this post has an example given by Sean

Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG
Helper II

What I need to do is this: I have a running total (measure) that needs to be based only on the last value in a month:

Total =
CALCULATE (
SUM ( 'Trends'[Total Unresolved] ),
FILTER (
ALL ('Trends'[timestamp] ),
'Trends'[timestamp] <= MAX ( 'Trends'[timestamp] )
)
)

I don't want to sum all values but only the last one in a month. I can create another measure using CLOSINGBALANCEMONTH function but how can I reference it in the measure above?

Filtered Total = CLOSINGBALANCEMONTH(MAX('Trends'[Total Unresolved]),'Trends'[timestamp])

I tried this but it returns an error because SUM expects a column:

Total =
CALCULATE (
SUM ([Filtered Total] ),
FILTER (
ALL ('Trends'[timestamp] ),
'Trends'[timestamp] <= MAX ( 'Trends'[timestamp] )
)
)

Thanks,

M.R.

Community Champion

Just remove the SUM.

Total = CALCULATE (
[Filtered Total],
FILTER (
ALL ('Trends'[timestamp] ),
'Trends'[timestamp] <= MAX ( 'Trends'[timestamp] )
)
)

Proud to be a Super User!

Helper II

I tried your suggestion, no error but I don't get a running total anymore, only discrete values...

Let me reformulate the problem, maybe my approach is wrong. Basically I need to get a conditional running total including only last date in a month:

DATE                   TOTAL          RUNNING TOTAL (as a Measure or calculated Column)

01/03/2016               10                             0

01/21/2016               17                             0

01/29/2016               3                               3

02/05/2016               22                             3

02/15/2016               121                          124

03/01/2016               101                           124

03/10/2016               56                             124

03/30/2016               43                            167

I need to calculate the running total only for highlited dates (last date in a month)

Community Champion

I'm not sure I understand what you want, but I'll take a stab at it. Are you saying you want a measure that returns 0 on any day that isn't the last day of the month, but returns the total for the whole month on the last day of the month?

Proud to be a Super User!

Helper II

Thank you for your help. I think this explains better what I am looking for:

DATE                   TOTAL                     This is the value to sum:                  This is the RUNNING TOTAL:

01/03/2016               10                             0                                                              0

01/21/2016               17                             0                                                              0

01/29/2016               3                               3                                                              3

02/05/2016               22                             0                                                              3

02/15/2016               121                          121                                                          124

03/01/2016               101                           0                                                             124

03/10/2016               56                             0                                                             124

03/30/2016               43                            43                                                            167

Basically I need a Running Total column that conditionally summarize the values for the last date in a month..

Community Champion

Your example is showing dates that aren't really the last date in the month, so my approach might not work for that. It's literally looking for the last calendar date of the month. Are you looking for this to only calculate on the last date of the month with a corresponding...whatever you're counting? Sale or whatever?

Proud to be a Super User!

Helper II

Sorry, I didn't see the new post, I am trying it now.

Helper II

Yes, exactly. I am looking tor the last date entry in a month and I need to add up the values. I shall test your code.

thanks

Community Champion

My way should give you the correct total* and only give it once per month. It will just give it on the last date of the month, even if the last date with an entry is earlier. To get the same behavior on an arbitrary date of the month the way you're looking for will probably be quite a bit more complicated, but I'll see what I can come up with.

*I have no idea what you're really trying to do with that CLOSINGBALANCEMONTH bit, so my version may give different results than you're looking for. My version should give the sum of all entries in a column for a given month. Adapt that part of the formula as necessary.

Proud to be a Super User!

Community Champion

OK, here's what I came up with. I feel like this formula is way more complicated than it needs to be but I've tested it and it works on my data set. I tested this on a set of daily email and phone call activities where I filtered out the last few days of every month. It calculates on the last date that I didn't remove, so that should match your case. For mine [Measure] is a distinctcount of activityIDs. You would just use whatever you're trying to total per month.

I think where you're using CLOSINGMONTHBALANCE you probably should be using TOTALMTD. CLOSINGMONTHBALANCE is IIRC more correctly used for finding the total at the end of an offset month for comparison, so if you wanted to compare March to January or something like that. If you just want to find the total for the month in context, TOTALMTD is your friend.

```Monthly Total =
VAR datecheck = FIRSTNONBLANK(DateTable[Date], 1)
RETURN IF(
CALCULATE(
MAX(DateTable[Date]),
FILTER(
ALL(DateTable),
DateTable[Month Number] = MONTH(datecheck) &&
[Measure] > 0
)
) <> datecheck,
BLANK(),
TOTALMTD(
[Measure],
DateTable[Date]
)
)```

Now, all that being said, you could just create a [Month of Year] field in your date table (you could either make this read as "January 2016" or the last date of the month, whatever you like), and use that instead of the date field. Then your formula would be much simpler:

```Monthly Total = TOTALMTD(
[Measure],
DateTable[Date]
)```

Proud to be a Super User!

Helper II

1. Created a new measure:

Monthly Total =
VAR datecheck = FIRSTNONBLANK('Trends'[action_timestamp], 1)
RETURN IF(
CALCULATE(
MAX(Trends'[action_timestamp]),
FILTER(
ALL('Trends'),
'Trends'[action_timestamp].[Month] = MONTH(datecheck) &&
'Santiago Trends'[Total1] > 0
)
) <> datecheck,
BLANK(),
TOTALMTD(
'Santiago Trends'[Total1],
'Santiago Trends'[action_timestamp]
)
)

I get an error:

A single value for column 'Total1' in table 'Trends' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

Community Champion

It looks like you're referencing the same table for everything. Do you not have a date table set up for time intelligence? None of that class of formulas will work right for you without one. https://www.sqlbi.com/articles/time-intelligence-in-power-bi-desktop/

Proud to be a Super User!

Helper II

I cannot create an additional table for dates, I already have a timestamp column in my table. I think it should be an algorhytm to do the following:

1. For each month+year in the [Timestamp] column, identify the last entry in that month

2. For this specific date, select the dollar value, [Total]

3. Keep adding these [Total] values as a [Running Total]

Community Champion

Of course you can create another table. That's how time intelligence works. You create a date table with a continuous series of dates, plus columns for any extra filters you'd like (such as Month, Quarter, Week, whatever), then you make a relationship between that table's Date column and the timestamp column in your other table.

Proud to be a Super User!

Helper II

Yes, I should probably create a date table, this is a very good suggestion.

I am very new to Power BI, less than one month...

Community Champion

Better read up on how Time Intelligence formulas work.

http://www.powerpivotpro.com/category/time-intelligence/

https://www.sqlbi.com/articles/time-intelligence-in-power-bi-desktop/

http://exceleratorbi.com.au/power-pivot-calendar-tables/

Here's a simplified version of the date table I use. Go to Get Data, hit Blank Query, then open the Advanced Editor, delete everything and paste this in:

```let
Source = List.Dates,
#"Created Date List" = Source(
#date(2014, 1, 1),
2192,
#duration(1, 0, 0, 0)
),
#"Table from List" = Table.FromList(
#"Created Date List",
Splitter.SplitByNothing(),
null,
null,
ExtraValues.Error
),
#"Table from List",
"Index",
1,
1
),
#"Renamed Date Column" = Table.RenameColumns(
{{"Column1", "Date"}}
),
#"Changed Date Type" = Table.TransformColumnTypes(
#"Renamed Date Column",
{{"Date", type date}}
),
#"Changed Date Type",
"Year",
each Number.From(
Date.Year([Date])
)
),
"Quarter Year",
each Number.ToText([Year]) &
" Q" &
Number.ToText(
Date.QuarterOfYear([Date]),
"00"
)
),
"Month Number",
each Date.Month([Date])
),
"Month",
each Date.ToText([Date],"MMM")
),
"Month of Year",
each Date.ToText([Date],"MMM") & " " & Number.ToText([Year])
),
"MonthIndex",
each if [Index] = 1
then 1
else if [Date] = Date.StartOfMonth([Date])
then List.Count(
List.Distinct(
List.FirstN(
#"Added Month of Year"[Month of Year],
[Index] - 1
)
)
) + 1
else List.Count(
List.Distinct(
List.FirstN(
#"Added Month of Year"[Month of Year],
[Index] - 1
)
)
)
),
"DayNum",
each Date.Day([Date])
),
"WeekDay",
each Date.DayOfWeek([Date])
),
"Day",
each Date.ToText([Date],"ddd")
),
#"Changed Types" = Table.TransformColumnTypes(
{
{"Year", Int64.Type},
{"Month Number", Int64.Type},
{"MonthIndex", Int64.Type},
{"WeekDay", Int64.Type}
}
)
in
#"Changed Types"```

MonthIndex is meant to be applied to Month of Year under Sort by Another Column. Same for WeekDay and Day, and for Month Number and Month. That way the day names and month names appear in the right order. Create a relationship between DateTable[Date] and  'Santiago Trends'[action_timestamp].

Your requirement that this should show on the last date with an entry makes this far more difficult and I'm not sure I understand the point of it. If you could just use a series of months this would be easy. Use the DateTable[Month of Year] column. You want the last entry for each month, and a running total of that for the year. You'll need two measures. First:

```Last Entry = CALCULATE(
LASTNONBLANK( 'Santiago Trends'[action_timestamp], 1),
DATESMTD(DateTable[Date])
)```

Now create a matrix or table visual with DateTable[Year] as the first row and DateTable[Month of Year] as the second. Add one more measure:

```Monthly Total = IF(HASONEVALUE(DateTable[Month of Year]),
[Last Entry],
SUMX(
VALUES(DateTable[Month of Year]),
[Last Entry]
)
)```

That measure should be the only one you actually place into the values section of your matrix. It will give the last entry for each month, then at the bottom you'll get the total for each year. Using my two result examples from earlier, Result B would now look like:

Month of Year        Montly Total

January 2016         \$10

February 2016       \$7

TOTAL                   \$17

Proud to be a Super User!

Helper II

Thank you vey much KHorseman for looking into this, I shall try your code tomorrow.

Regards

Community Champion

I just realized I made a mistake in my code. That code for Last Entry will return the date of the last entry, not the last entry itself. Looking back at your original formula I think it should be

```Last Entry = CALCULATE(
LASTNONBLANK( 'Trends'[Total Unresolved], 1),
DATESMTD(DateTable[Date])
)```

...or whatever the column is with the number you're trying to tally. Not the timestamp column.

Now, if you really really want to have that last date with an entry, add a third measure:

```Last Date = CALCULATE(
MAX('Santiago Trends'[action_timestamp]),
DATESMTD(DateTable[Date])
)```

You could use my original mistake version of Last Entry, but this version also works, and is slightly easier to type.

Proud to be a Super User!

Helper II

Finally got it working! Thanks again for your help KHorseman!