Using DATEDIFF in measure

[Anonymous]

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 a one year period and display the sales of store that have not been opened within that year. I also have a year slicer (which is properly working without the filter) like so:

When I try to add the desired filter, the graph display: "Cannot display values". Here is my dax measure:

Sales Filter = CALCULATE( SUM(Sales[sale]),

     FILTER(Sales,

          DATEDIFF(RELATED(Stores[OpenedDate]), VALUES( Sale[Date] ), YEAR) > 1

     )

)

Thanks for the help in advance!

[AlB]

@Anonymous 

Your code above is missing a closing parenthesis. Not sure if that's  a typo when copying it here or if it is the actual problem.

Do make sure you build the condition correctly according to what you want. Right now you are selecting stores that both opened AND went thru a refit  in another year.

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 

[Anonymous]

Thanks so much! It now works as attended.

[daxer-almighty]

Here's the article I alluded to above: Understand star schema and the importance for Power BI - Power BI | Microsoft Docs

[daxer-almighty]

// 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

[AlB · Accepted solution]

@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 

[Anonymous]

Thank you. The Dax you provided works as a intented. I tried adding an additional filter in the FILTER using LOOKUPVALUE instead of RELATE to connect a 'Refit' table that also has an 'OpenedDate' column in an attempt to filter stores that have gone thru a refit as well. This time however the code nolonger seems to work:

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_ &&

                    YEAR ( LOOKUPVALUE( Refit[OpenedDate], Refit[StoreID], Sales[StoreID] ) <> currentYear_

          )

     )

[AlB]

Hi @Anonymous 

Let's try and clarify. You say you want to display the sales of store that have not been opened within that year

What does that mean exactly and what fields would be used to determine it? Please provide examples to clarify: shops that will be included and shop that will be excluded

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 

[Anonymous]

Hey! Yes that is precisely what I am attempting to do. To display the sales of store that have not been opened within that year as you say. 

In the "Stores" table, a column named "OpenedDate" displays the opening date of the store (dd-mm-year). Whether or not a store should be dispayed in the sales is determined if its opening date is the same as the selected year. 

In other words, with the year slicer on year 2020, the store of ID 1 with an opening date of 01-01-2020 should be excluded while store of ID 2 with an opening date of 01-01-2015 should not.