-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Sum sales for max date in a month?
- Thread starter Dan80
- Start date
Hi Dan,
You can use an expression like
= CALCULATE( [Sales], LASTDATE('Date'[Date]) )
to get sales on the max date in the current filter context, e.g. in March it would give you sales on 31 March.
If you instead wanted sales on the last date that appears in your fact table, you could change 'Date'[Date] to FactTable[Date].
The reason LASTDATE works but MAX doesn't is that LASTDATE returns a table with a single date which retains lineage, so can be used for filtering, but MAX just returns a scalar that can't be used for filtering.
Cheers,
Owen
You can use an expression like
= CALCULATE( [Sales], LASTDATE('Date'[Date]) )
to get sales on the max date in the current filter context, e.g. in March it would give you sales on 31 March.
If you instead wanted sales on the last date that appears in your fact table, you could change 'Date'[Date] to FactTable[Date].
The reason LASTDATE works but MAX doesn't is that LASTDATE returns a table with a single date which retains lineage, so can be used for filtering, but MAX just returns a scalar that can't be used for filtering.
Cheers,
Owen
Upvote
0
Similar threads
- Question
