Dynamic / Flexible Time Frames - User Input

[Anonymous]

Hi

I have a Calendar table (1 row per day) with an index and a a 'last year' index.  I also have Fact Sales table with the date index and I am using USERELATIONSHIP to calculate sales for this year and for last year.

I want to take this to the next level though and set it up in a way so that a user can input the 'offset' they wish to compare sales against e.g. it could be this week vs last week, this week vs 4 weeks ago, etc..

I could add loads of pre-calculated index columns to my calendar table and use a Slicer and the SWITCH function to allow the user to pick which relationship to use, but it seems over excesive and limited to only what I pre-define for them.

Is there a way a user could pick from a slicer to dynamically determin the index in the column with a calculation?  E.g. The user picks '-14 days' in the offset slicer and the dynamic column = ' [index]-14 ' ?  See example below in red.  This column would change if a user picked a different offset.

I can get this to work as a measure but not as a calculated column.  As a measure I can't use it in a relationship in my model. 

Example calendar:

Date	Index	LY Index	Dynamic Index
16 June 2020	536	172	529
17 June 2020	537	173	530
18 June 2020	538	174	531
19 June 2020	539	175	532
20 June 2020	540	176	533
21 June 2020	541	177	534
22 June 2020	542	178	535
23 June 2020	543	179	536
24 June 2020	544	180	537
25 June 2020	545	181	538
26 June 2020	546	182	539
27 June 2020	547	183	540
28 June 2020	548	184	541
29 June 2020	549	185	542
30 June 2020	550	186	543

Example measures:

Sales (This Year) = CALCULATE ( sum (FACTSales[Sales]) , USERELATIONSHIP ( DIMDateTbl[Index] , 'FACTSales'[DIMDateTbl.Index] ) )

Sales (Last Year) = CALCULATE ( sum (FACTSales[Sales]) , USERELATIONSHIP ( DIMDateTbl[LY Index] , 'FACTSales'[DIMDateTbl.Index] ) )

If anyone can help it would be massively appreciated! 

I have a multitude of user scenarios to answer with this so it would be great to make this work dynamically!

Thanks,

Si

[Anonymous]

Thanks for your help @amitchandak @v-alq-msft @Ashish_Mathur  !!!

[Anonymous · Accepted solution]

Hi

Just to let you all know I have resolved this so I now have a slicer on my report that allows the user to select the time 'offset' they want to compare sales against.  E.g. This week vs X number of weeks ago.

• To do this I created a a summary of my Calendar dimension table
• Added a column with '1' in every row
• Merge the table with itself on the '1' so every row is joined to every row in the table (new length of table = length of original table x length of original table)
• Created a new column called 'Date Diff' where I subtract the date from the joined date
• I then filter out any rows where the date difference is a positive value

let
    Source = DIMDateTbl,
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Index", "Date"}),
    #"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Flat Join", each 1),
    #"Merged Queries" = Table.NestedJoin(#"Added Custom", {"Flat Join"}, #"Added Custom", {"Flat Join"}, "Added Custom", JoinKind.FullOuter),
    #"Expanded Added Custom" = Table.ExpandTableColumn(#"Merged Queries", "Added Custom", {"Index", "Date"}, {"Join Index", "Join Date"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Added Custom",{"Flat Join"}),
    #"Added Custom2" = Table.AddColumn(#"Removed Columns", "Date Diff", each Duration.Days( [Date] - [Join Date])  ),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom2", each [Date Diff] < 0),
    #"Reordered Columns" = Table.ReorderColumns(#"Filtered Rows",{"Date Diff", "Date", "Index", "Join Date", "Join Index"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Reordered Columns",{{"Date Diff", type number}})
in
    #"Changed Type"

In the model I sit this new table between my sales table and my original calendar table - This means the slicers build from my original calendar dimension table still work.

Data Model:  I have a relationship between the Index of my original Cal Dimension table and the 'join index' in this new table.  And then from this new table I have an INACTIVE relationship from it's 'index' to the date index in my sales table.

The Sales measure uses USERELATIONSHIP and looks like this:

Sales (Filter) = CALCULATE( sum (FACTSales[Sales]) , USERELATIONSHIP( 'DIMDateTbl Custom Relationship'[Index] , 'FACTSales'[DIMDateTbl.Index] ) )

('DIMDateTbl Custom Relationship' is my new table)

The 'Offset Slicer':  IMPORTANT!  I can now add my slicer to reference the 'Date Diff' column in my new table, but it is very important that you have this set to 'single select' so users cannot select multiple 'offsets'.

Then just for presentation I added a new calculated column based on my 'Date Diff' column to make the slicer read better:  

Offset Selection = If ( MOD('DIMDateTbl Custom Relationship'[Date Diff],7) = 0 , format ('DIMDateTbl Custom Relationship'[Date Diff]/7,"#") & " Week" , format ('DIMDateTbl Custom Relationship'[Date Diff],"#") & " Day" ) & IF('DIMDateTbl Custom Relationship'[Date Diff] = -1 || 'DIMDateTbl Custom Relationship'[Date Diff] = -7 , "" , "s" )

Hope this makes sense!

Thanks,

Si

[Anonymous]

PS The filter direction in the relationship between the Index of my original Cal Dimension table and the 'join index' in the new table is set to BOTH.  

[Ashish_Mathur]

Hi,

This should always be written as a measure, never as a column formula.

[v-alq-msft]

Hi, @Anonymous 

A calculated column can't dynamically interact with user's selection. A measure will reflect the selection of the slicer in time, and the column is refreshed only after loading and clicking the Refresh button, and cannot interact with other visual interactions. 

You have to define a calculated column whenever you want to do the following:
1. Place the calculated results in an Excel Slicer, or see results in Rows or Columns in a pivot table, or use the result as a filter condition in a DAX query.
2. Define an expression that is strictly bound to the current row. 

3. Categorize text or numbers. 

You need to define a measure whenever you want to display resulting calculation values that reflect user selections and see them in the Values area of pivot tables, for example:
1. When you calculate profit percentage of a pivot table selection.
2. When you calculate ratios of a product compared to all products but keeping the filter both by year and region. You can express some calculations both with calculated.

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , what are you trying to achieve with index here. 

If you want to compare year vs year

https://community.powerbi.com/t5/Community-Blog/Decoding-Direct-Query-in-Power-BI-Part-1-Time-Intelligence-in/ba-p/922885

If you want to compare data using Index/Rank

https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123

https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p/881739