Forum Discussion

LakshX413's avatar
LakshX413
Regular Visitor
1 year ago
Solved

Keep all previous and current items & values in table regardless of date filter

Hi All - I am running into a weird issue: I have a requirment to show previous inventory and current inventory in a view for comparision. I created a matrix to lay down inventory by Platform (PLx) ->...
  • danextian's avatar
    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