Carry forward cylinder inventory levels when filtering by month

[BudMan512]

@lbendlin 

I have a Power BI report which tracks the number of cylinders Delivered and Picked up by date.  It also contains a Begin Inv column and a Current Inv column (Ending).  The 'Current Inv' DAX was generously provided to me by @lbendlin.  The Begin and Current inventories can be seen in the report below as can the very first delivery made to the customers, which are highlighted in yellow.  This first delivery establishes the initial Current Inv which is recalculated with every subsequant delivery as does the Begin Inv. 

I would like to be able to filter the deliveries by Year and Month.  All other transactions would not render but I need the Current Inv and Begin Inv values to be retained as calculated from their very first delivery.  In addition I need the total of the Delivered cylinders as determined by the Date filter.  Below is a screenshot of the report I need.

I have also provided a link to the report as it stands now.

Thank you in advance for any assistance provided.

Bud

https://1drv.ms/u/s!An-c-kQsqoNPgnW5Ylfm_phXgwI2?e=Wb5jVT

[talespin]

Clutter

[BudMan512]

@talespin 

Thanks for sending the DAX for my problem.  It worked as requested.

I wonder if I could trouble you for one modification.

Would you be able to provide DAX that would copy the final 'Begin Inventory' of the filtered period up to the summary rows containing the Delivered and Picked UP cylinders.? If you could do the same for the 'Current Inventory' that would nice but not required.

Here are the values I would like to move up.

Here is the final result

And here is the final result with the data drilled up.

Thanks so much,

Bud

[talespin]

hi @BudMan512 

Incorrect Solution.

[BudMan512]

@talespin 

Hey, I just found an issue with my Begin Inventory.  When using that value from the last delivery of the month, the math doesn't always add up as seen below.  What is needed for the Begin Inventory for the summary row is the Begin Inventory value for the very first transaction of the month.  So in the below example, the Begin Inventory should be 20 and not 22.  Then the math works out on that summary row.  Sorry, I should have caught that. 

Thanks,

Bud

[talespin · Accepted solution]

hi @BudMan512 

Please test it out thoroughly, it blew my brain fuse couple of times 😄

Removing other posts(Clutter).

Uploaded file with GrandTotals and inventory issue fixed.

https://drive.google.com/file/d/1jRX84Kl-Mm9Bzh548cU8MDqiTGkYQQSb/view?usp=sharing

-------------------------------------------------------------------------

Begin Inv =

VAR _MinDate = CALCULATE(MIN(Transactions[Date]))

VAR _Delivered = CALCULATE( SUM(Transactions[Delivered]), ALLEXCEPT(Transactions, Transactions[Customer]), Transactions[Date] < _MinDate)

VAR _Pickedup = CALCULATE( SUM(Transactions[Picked Up]), ALLEXCEPT(Transactions, Transactions[Customer]), Transactions[Date] < _MinDate)

VAR _BeginInventory = _Delivered - _Pickedup

RETURN IF(

            HASONEFILTER(Transactions[Customer]),

            IF(ISBLANK(_BeginInventory), 0, _BeginInventory),

            SUMX( VALUES(Transactions[Customer]),      

                VAR _Customer = [Customer]

                VAR _MinDateCustYrMth = CALCULATE( MIN(Transactions[Date]), Transactions[Customer] = _Customer)                                        

                VAR _GTDelivered = CALCULATE( SUM(Transactions[Delivered]), REMOVEFILTERS(), Transactions[Customer] = _Customer && Transactions[Date] < _MinDateCustYrMth)

                VAR _GTPickedup = CALCULATE( SUM(Transactions[Picked Up]), REMOVEFILTERS(), Transactions[Customer] = _Customer && Transactions[Date] < _MinDateCustYrMth)

                RETURN COALESCE((_GTDelivered - _GTPickedup),0)

            )

)

---------------------------------------------------------------------------

Current Inv =

VAR _MaxDate = CALCULATE(MAX(Transactions[Date]))

VAR _Delivered = CALCULATE( SUM(Transactions[Delivered]), ALLEXCEPT(Transactions, Transactions[Customer]), Transactions[Date] <= _MaxDate)

VAR _Pickedup = CALCULATE( SUM(Transactions[Picked Up]), ALLEXCEPT(Transactions, Transactions[Customer]), Transactions[Date] <= _MaxDate)

RETURN IF(

            HASONEFILTER(Transactions[Customer]),

            _Delivered - _Pickedup,

            SUMX(

                    VALUES(Transactions[Customer]),      

                    VAR _Customer = [Customer]                

                    VAR _MaxDateCustYrMth = CALCULATE( MAX(Transactions[Date]), Transactions[Customer] = _Customer)

                    VAR _GTDelivered = CALCULATE( SUM(Transactions[Delivered]), REMOVEFILTERS(), Transactions[Customer] = _Customer && Transactions[Date] <= _MaxDateCustYrMth)

                    VAR _GTPickedup = CALCULATE( SUM(Transactions[Picked Up]), REMOVEFILTERS(), Transactions[Customer] = _Customer && Transactions[Date] <= _MaxDateCustYrMth)

                    RETURN _GTDelivered - _GTPickedup

            )

)

------------------------------------------------------------------------

[BudMan512]

@talespin 

Hey, I could see how this might blow a fuse or two.  So thanks a lot, it provides just what I needed.  I thought it would be a simple change, so sorry for the brain pain, but it looks great.

Best Regards,

Bud

[talespin]

hi @BudMan512 

You're welcome. Its ok, it was a good puzzle.

[BudMan512]

@talespin 

Hi Talespin,

This is awesome.  I do appreciate your time and brain power.

I hate to ask about adding the Max values in grand total.  If by this you mean adding the  Begin and Current totals for the Customers, that would be great.

Thanks,

Bud

[v-yangliu-msft]

Hi  @BudMan512 ,

You can use IF()+Isinscope to determine the level in the matrix visual to customize the rules

Refer to:

IF function (DAX) - DAX | Microsoft Learn

ISINSCOPE function (DAX) - DAX | Microsoft Learn

Here are the steps you can follow：

1. Create measure.

Measure =
IF(
    ISINSCOPE('Transactions'[Date]),
    SUM('Transactions'[Delivered]),
    IF(
        ISINSCOPE('Transactions'[Customer]),        SUMX(FILTER(ALL('Transactions'),'Transactions'[Customer]=MAX('Transactions'[Customer])&&YEAR('Transactions'[Date])=YEAR(TODAY())&&MONTH('Transactions'[Date])=MONTH(TODAY())),[Delivered])
          ,
        SUMX(ALLSELECTED('Transactions'),[Delivered])
        ))

Flag =
var _today=TODAY()
var _mindate=MINX(FILTER(ALLSELECTED('Transactions'),'Transactions'[Customer]=MAX('Transactions'[Customer])),[Date])
return
IF(    YEAR(MAX('Transactions'[Date]))=YEAR(_today)&&MONTH(MAX('Transactions'[Date]))=MONTH(_today) || MAX('Transactions'[Date])=_mindate,1,0)

2. Place [Flag]in Filters, set is=1, apply filter.

3. Result:

Best Regards,

Liu Yang

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

[123abc]

It seems like you're looking to filter your Power BI report by Year and Month while retaining the cumulative inventory calculations (Begin Inv and Current Inv) based on the initial delivery, and also display the total delivered cylinders based on the filtered date range.

To achieve this in Power BI, you can follow these steps:

1. Create a Date Table: Ensure you have a date table in your Power BI model. This table should contain a date column that you can use to filter your data.

2. Use CALCULATE Function: Use the CALCULATE function along with FILTER to filter the data based on the selected Year and Month while preserving the cumulative inventory calculations.

3. Calculate Total Delivered Cylinders: Create a measure to calculate the total delivered cylinders based on the filtered date range.

Here's a sample DAX formula for calculating the total delivered cylinders:

Total Delivered Cylinders =
CALCULATE(
SUM('YourTableName'[Delivered Cylinders]), -- Replace 'YourTableName' with your actual table name
ALL('YourDateTable'[Date]), -- Remove filters on the Date table
'YourDateTable'[Date] <= MAX('YourDateTable'[Date]) -- Apply filter for selected date or date range
)

1. Retain Cumulative Inventory: Ensure that the calculations for Begin Inv and Current Inv are not affected by the date filters. You might need to adjust the DAX expressions for Begin Inv and Current Inv accordingly. These expressions should reference the entire dataset rather than being affected by any filters.

If you face challenges with specific DAX expressions or need further assistance with implementing the filtering, feel free to provide more details or share the DAX expressions you're currently using, and I can help you refine them for your requirements.

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

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[BudMan512]

@123abc 

Thanks for your thoughtful reply.

I appreciated your time.

Bud

[v-yangliu-msft]

Hi  @BudMan512 ,

Here are the steps you can follow：

1. Create measure.

Flag =
var _today=TODAY()
return
IF( YEAR(MAX('Transactions'[Date]))=YEAR(_today)&&MONTH(MAX('Transactions'[Date]))=MONTH(_today),1,0)

2. Place [Flag]in Filters, set is=1, apply filter.

3. Result:

If you also want to keep the Current Inv and Begin Inv values calculated from the first delivery, you can use the following formula:

Flag =
var _today=TODAY()
var _mindate=MINX(FILTER(ALLSELECTED('Transactions'),'Transactions'[Customer]=MAX('Transactions'[Customer])),[Date])
return
IF(    YEAR(MAX('Transactions'[Date]))=YEAR(_today)&&MONTH(MAX('Transactions'[Date]))=MONTH(_today) || MAX('Transactions'[Date])=_mindate,1,0)

Best Regards,

Liu Yang

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

[BudMan512]

@v-yangliu-msft 

Hi Lui,

Thank you for your time spent helping me with my problem.

I appreciate it.

Bud

[BudMan512]

@v-yangliu-msft 

Hi Liu,

Thanks for taking the time to help me with this problem.

There does appear to be an issue with the sum of delivered cylinders for the filtered date range.

For customer A it should be a sum of 23 cylinders delivered.

For customer B it should be a sum of 9 cylinders.

I appreciate your assistance.

Regards,

Bud