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!
