Calculating values for periods based on attributes of a date dimension

DAX DAX Calculations
1

Hi guys, I know this is probably very simple but I just can’t get it working, here is the situation:

My fact table data is at the grain of Date > Store > Department. Each fact table row has a DateID which keys to the date dimension dimension_Date. The sale amount in this case is called “Actual”.

Dimension_Date has attributes for FiscalWeek, DayOfWeek, LastYearDateID. (Amongst many others).

I am wanting to calculate the value for “same day last week” and “same day last year”. It should be easy as these attributes are already on the date table! (I would have thought).

Here is what I have tried:

Actual LW = 
VAR CurrentWeek = SELECTEDVALUE(Dimension_Date[FiscalWeek])
VAR CurrentDay = SELECTEDVALUE(Dimension_Date[DayOfWeek])

RETURN
    CALCULATE([Actual],
        FILTER(ALL(Dimension_Date),
            Dimension_Date[DayOfWeek] = CurrentDay &&
            Dimension_Date[FiscalWeek] = CurrentWeek - 1))

This just returns all blanks though!

Any help gratefully received.

Jamie

2

@Jamie It appears that SELECTEDVALUE may not be returning anything at all as the evaluation context where it is evaluated must have multiple values for the referenced column, and in case of multiple values SELECTEDVALUE doesn’t return anything.

Do one thing, after first argument in SELECTEDVALUE enter a day and week number and then check if it returns anything.

Question: Are you using the measure in a card visual?

3

Hi and thanks so much for your answer. No, I am using the measure in a table. I need the “Actual” value for today and a column for the Actual value for one week ago. I have realised it should be even simpler than I thought. Every row in the fact table has a DateID and it also has LastWeekDateID.

4

@Jamie Oh, in that case try this:

Actual LW =
VAR CurrentWeek =
    RELATED ( Dimension_Date[FiscalWeek] )
VAR CurrentDay =
    RELATED ( Dimension_Date[DayOfWeek] )
RETURN
    CALCULATE (
        [Actual],
        Dimension_Date[DayOfWeek] = CurrentDay,
        Dimension_Date[FiscalWeek] = CurrentWeek - 1,
        REMOVEFILTERS ( FactTable )
    )
5

Hi - I have an error:

The column ‘Dimension_Date[FiscalWeek]’ either doesn’t exist or doesn’t have a relationship to any table available in the current context.

I mean it definitely exists… the two tables are related on DateID.

(And thank you so much for your help so far!)

J

6

@Jamie Is there an active relationship between Dates and Fact Table? Can you please show the diagram view of the model.

7

snip
snip1356×952 86 KB

8

No idea, what is happening here, is it possible for you to attach the file?

9

Could try changing the Cross filter direction to Single. Rarely use “Both”.

Thanks
Jarrett

10

Thanks for posting your question @Jamie. To receive a resolution in a timely manner please make sure that you provide all the necessary details on this thread.

Here is a potential list of additional information to include in this thread; demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

Including all of the above will likely enable a quick solution to your question.

11

Hi @Jamie, we’ve noticed that no response has been received from you since the 9th of November. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!