Forum Discussion
Reference a measure in another measure
- Anonymous10 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.
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...
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
- Anonymous10 years agoNot applicable
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.
- MR200110 years ago
Helper II
Finally got it working! Thanks again for your help KHorseman!
- MR200110 years ago
Helper II
Thank you vey much KHorseman for looking into this, I shall try your code tomorrow.
Regards