Forum Discussion
Keep all previous and current items & values in table regardless of date filter
- 1 year ago
Hi LakshX413
This is one of the reasons why using separate dimensions tables is necessary.
Use a separate week_start_date dimension table. You can create a calculated table with this
weekdimensiontable = DISTINCT ('Units by Platform 1'[week_start_date])Create a separate dimension table for platform units to ensure they remain unaffected by week filters. If columns from the fact table are used, only rows within the selected date range will be displayed in visuals. This means that if P15 has no data for March 16, it will not appear.
Create a one-to-many single direction relationship from these dim tables to your fact and use these column in your visuals. Use this measure:
previous_inventory = VAR PreviousDate = CALCULATE ( -- Find the most recent week_start_date before the current one MAX ( 'weekdimensiontable'[week_start_date] ), FILTER ( ALL ( 'weekdimensiontable' ), -- Consider all dates in the dimension table 'weekdimensiontable'[week_start_date] < MAX ( 'weekdimensiontable'[week_start_date] ) -- Only include dates earlier than the current one ) ) RETURN ( CALCULATE ( -- Retrieve the inventory sum for the identified previous week SUM ( 'Units by Platform 1'[Inventory] ), 'dimensiontable'[week_start_date] = PreviousDate ) )If this doesn't work, please provide a workable sample data (not an image). You may post a link to Excel or a sanitized copy of your PBIX stored in the cloud. Please refer to this sticky post on how to your questions answered quickly - https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
Hi LakshX413
This is one of the reasons why using separate dimensions tables is necessary.
Use a separate week_start_date dimension table. You can create a calculated table with this
weekdimensiontable = DISTINCT ('Units by Platform 1'[week_start_date])
Create a separate dimension table for platform units to ensure they remain unaffected by week filters. If columns from the fact table are used, only rows within the selected date range will be displayed in visuals. This means that if P15 has no data for March 16, it will not appear.
Create a one-to-many single direction relationship from these dim tables to your fact and use these column in your visuals. Use this measure:
previous_inventory =
VAR PreviousDate =
CALCULATE (
-- Find the most recent week_start_date before the current one
MAX ( 'weekdimensiontable'[week_start_date] ),
FILTER (
ALL ( 'weekdimensiontable' ),
-- Consider all dates in the dimension table
'weekdimensiontable'[week_start_date]
< MAX ( 'weekdimensiontable'[week_start_date] ) -- Only include dates earlier than the current one
)
)
RETURN
(
CALCULATE (
-- Retrieve the inventory sum for the identified previous week
SUM ( 'Units by Platform 1'[Inventory] ),
'dimensiontable'[week_start_date] = PreviousDate
)
)
If this doesn't work, please provide a workable sample data (not an image). You may post a link to Excel or a sanitized copy of your PBIX stored in the cloud. Please refer to this sticky post on how to your questions answered quickly - https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523