Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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
Solved! Go to 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
Clutter
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
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
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
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 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
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
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:
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.
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.
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
)
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.
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
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
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
23 | |
21 | |
20 | |
14 | |
11 |
User | Count |
---|---|
43 | |
33 | |
25 | |
24 | |
23 |