Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
Solved! Go to 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.
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
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.
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
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.
Hi,
This should always be written as a measure, never as a column formula.
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.
@Anonymous , what are you trying to achieve with index here.
If you want to compare year vs year
If you want to compare data using Index/Rank
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |