Forum Discussion

MR2001's avatar
MR2001
Icon for Helper II rankHelper II
10 years ago
Solved

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.
Need Help
  • Anonymous's avatar
    Anonymous
    10 years ago

    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.

MR2001's avatar
MR2001
Icon for Helper II rankHelper II
10 years ago

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.

Anonymous's avatar
Anonymous
Not applicable
10 years ago

Just remove the SUM.

 

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

  • MR2001's avatar
    MR2001
    Icon for Helper II rankHelper II
    10 years ago

    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)

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      10 years ago

      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?

      • Anonymous's avatar
        Anonymous
        Not applicable
        10 years ago

        I'm going to roll with that concept because the filter seems like a fun challenge to write. I'll bet someone else here can come up with better DAX than this but here's my first pass at it:

         

        Monthly Total = IF(
	FIRSTNONBLANK(DateTable[Date], 1) <> EOMONTH(FIRSTNONBLANK(DateTable[Date], 1), 0),
	BLANK(),
	CLOSINGBALANCEMONTH(
		SUM(Table[Column]),
		DateTable[Date]
	)
)

        I've made the code as generic as possible, assuming you have a proper date table set up for time intelligence. I'm also guessing that your example CLOSINGBALANCEMONTH code was an earlier attempt at getting the end of month results you're looking for, and that's the reason for the MAX in it. This measure will only calculate on the last day of the month, so if you put it on any visual with that DateTable[Date] column as the rows or axis or whatever, it will only show those end of month dates by default. The other dates won't even show up. If you want to see all the dates with a bunch of zeroes, replace the part that says Blank() with a 0.

         

        Anonymous got any other ideas for this one?