Forum Discussion
Anonymous
5 years agoNot applicable
Using DATEDIFF in measure
Hello! I've spent some time attempting to use DATEDIFF in a dax measure but failed to do so. Here's the basics; I have two tables, "Sales" and "Stores". I am trying to display sales over time within ...
- 5 years ago
Anonymous
Sales Filter = VAR CurrentYear_ = YEAR ( MAX ( DateT[Date] ) ) // This could also be: VAR CurrentYear_ = MAX(DateT[Year]) RETURN CALCULATE ( SUM ( Sales[sale] ), FILTER ( Sales, YEAR ( RELATED ( Stores[OpenedDate] ) ) <> currentYear_ ) )Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
daxer-almighty
5 years agoSolution Sage
// For this to work you have to have a correct model.
// If you want to see what a correct model means you
// can ask Google this phrase "star schema in power bi"
// and a Microsoft page should appear near the top of the
// page, if not at the top. Please do READ IT.
// You need at least 3 tables with the following
// characteristics:
// 1. Sales table that will be your fact table (hidden).
// 2. Stores table that will hold all your stores.
// this is your dimension.
// 3. Dates table that will be the calendar in the
// model.
// Of course, a correct model will also very likely
// have a dimension called Customers. The Date table
// will join to the fact table on the sales date.
// I think in your case the column with the sales date
// is called just Sales[Date]. With all this in place,
// you can then write:
[Sales Filtered] =
// The assumption is that the period in the
// current context does not span more than
// 1 year. Otherwise the measure returns BLANK.
var __currentYear = SELECTEDVALUE( Dates[Year] )
// This variable will store StoreID's of stores
// as visible in the current context but such
// that have their Stores[OpenedDate] before
// the __currentYear.
var __storesThatOpenedBeforeTheYear =
CALCULATETABLE(
DISTINCT( Stores[StoreID] ),
KEEPFILTERS(
YEAR( Stores[OpenedDate] ) < __currentYear
)
)
var __output =
CALCULATE(
SUM( Sales[Sale] ),
__storesThatOpenedBeforeTheYear
)
return
__output