cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MR2001
Helper II
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
KHorseman
Community Champion
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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

29 REPLIES 29

@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

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.

KHorseman
Community Champion
Community Champion

Just remove the SUM.

 

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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)

 

 

 

KHorseman
Community Champion
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?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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..

KHorseman
Community Champion
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?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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

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

KHorseman
Community Champion
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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




KHorseman
Community Champion
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]
)




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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.

KHorseman
Community Champion
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/





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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]

 

 

KHorseman
Community Champion
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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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...

KHorseman
Community Champion
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
	),
	#"Added Index" = Table.AddIndexColumn(
		#"Table from List",
		"Index",
		1,
		1
	),
	#"Renamed Date Column" = Table.RenameColumns(
		#"Added Index",
		{{"Column1", "Date"}}
	),
	#"Changed Date Type" = Table.TransformColumnTypes(
		#"Renamed Date Column",
		{{"Date", type date}}
	),
    #"Added Year" = Table.AddColumn(
		#"Changed Date Type",
		"Year",
		each Number.From(
			Date.Year([Date])
		)
	),
	#"Added Quarter" = Table.AddColumn(
		#"Added Year",
		"Quarter Year",
		each Number.ToText([Year]) &
			" Q" &
			Number.ToText(
				Date.QuarterOfYear([Date]),
				"00"
			)
	),
    #"Added Month Number" = Table.AddColumn(
		#"Added Quarter",
		"Month Number",
		each Date.Month([Date])
	),
	#"Added Month" = Table.AddColumn(
		#"Added Month Number",
		"Month",
		each Date.ToText([Date],"MMM")
	),
	#"Added Month of Year" = Table.AddColumn(
		#"Added Month",
		"Month of Year",
		each Date.ToText([Date],"MMM") & " " & Number.ToText([Year])
	),
    #"Added MonthIndex" = Table.AddColumn(
		#"Added Month of 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
					)
				)
			)
	),
    #"Added DayNum" = Table.AddColumn(
		#"Added MonthIndex",
		"DayNum",
		each Date.Day([Date])
	),
	#"Added WeekDay" = Table.AddColumn(
		#"Added DayNum",
		"WeekDay",
		each Date.DayOfWeek([Date])
	),
    #"Added Day" = Table.AddColumn(
		#"Added WeekDay",
		"Day",
		each Date.ToText([Date],"ddd")
	),
    #"Changed Types" = Table.TransformColumnTypes(
		#"Added Day",
		{
			{"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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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

Regards

KHorseman
Community Champion
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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

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

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors