Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
BudMan512
Helper V
Helper V

Carry forward cylinder inventory levels when filtering by month

@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. 

 

BudMan512_0-1708988627716.png

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

 

BudMan512_1-1708989068214.png

 

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

 

 

1 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
            )
)
------------------------------------------------------------------------
 
talespin_0-1709355428871.png

 

View solution in original post

14 REPLIES 14
talespin
Solution Sage
Solution Sage

Clutter

@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.

BudMan512_0-1709221492585.png

Here is the final result

BudMan512_1-1709221578318.png

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

BudMan512_2-1709221878900.png

 

Thanks so much,

Bud

 

 

hi @BudMan512 

 

Incorrect Solution.

@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

 

BudMan512_0-1709304189076.png

 

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
            )
)
------------------------------------------------------------------------
 
talespin_0-1709355428871.png

 

@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

hi @BudMan512 

 

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

@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
Community Support
Community Support

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.

vyangliumsft_0-1709171272677.png

3. Result:

vyangliumsft_1-1709171272688.png

 

 

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
Community Champion
Community Champion

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.

@123abc 

Thanks for your thoughtful reply.

I appreciated your time.

Bud

v-yangliu-msft
Community Support
Community Support

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.

vyangliumsft_0-1709010697061.png

3. Result:

vyangliumsft_1-1709010697064.png

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)

vyangliumsft_2-1709010728515.png

 

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

@v-yangliu-msft 

Hi Lui,

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

I appreciate it.

Bud

@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

 

BudMan512_0-1709127583296.png

 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.