Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi All,
I need to calculate the Inventory based on the number of units that moved from Wholesale to Retail. I have a table that has the following
WholeSale Date | Retail Date | Units
----------------------------------------
1/02/2020 | | 1
1/15/2022 | 1/20/2022 | 1
1/20/2022 | 2/05/2022 | 1
The first and third records are in inventory as of Jan 31st, 2022 but not the second since it got sold before the end date and was not in the pipeline as of the end of the month ( Jan 31st 2022)
When an item enter the system, the wholesale date is filled and the retail date is not filled until the item is sold by the retailer.
So this means that if the Retail date is blank, it is still in the Inventory. As of today, if I pick sum the units which have retail date blank then I will get the Inventory.
The problem is how do I get the inventory as of a prior month ( or date) i.e Jan 2022. The records that need to be considers are from Jan 1, 2020 ( as I do not need to go back more than 3 yrs).
I am able to get the result by filtering the rows in a visual.
How can I create a measure to achieve the same result ?
See screenshot below. The inventory is 10,245.
I can provide a .pbix file for anyone that needs data
Thanks
SK
Solved! Go to Solution.
Xiaoxin,
I got it to work. There was a slight error in the conditions in the AND and OR section.
Below is the final formula that works and the screenshot is shown below.
Many thanks for making this work. I have to work on getting the median pipelength with different conditions but I understand the logic of the calculations now.
Once again many thanks
SK
Xiaoxin,
I got it to work. There was a slight error in the conditions in the AND and OR section.
Below is the final formula that works and the screenshot is shown below.
Many thanks for making this work. I have to work on getting the median pipelength with different conditions but I understand the logic of the calculations now.
Once again many thanks
SK
Thanks for your prompt reply. The number is close but not exact when I apply the filters to the table and display it in a table visual. Uploading the .pbix file that i used to create this again
Hi @skrishnaswamy,
I'd like to suggest you write a variable to extract the current row date values, then you can use it with the date function to calculate the previous date.
After these steps, you can write an expression to filter and summary records on your fact table based on date values. (these calculations can be dynamic changes based on the current row context date)
formula =
VAR currDate =
MAX ( Table[WholeSale Date] )
VAR prevDate =
DATE ( YEAR ( currDate ), MONTH ( currDate ) - 1, DAY ( currDate ) )
VAR _start =
DATE ( YEAR ( TODAY () ) - 3, MONTH ( TODAY () ), DAY ( TODAY () ) )
RETURN
IF (
currDate >= _start,
CALCULATE (
SUM ( Table[Sales] ),
FILTER (
ALLSELECTED ( Table ),
AND ( [WholeSale Date] <= prevDate, [WholeSale Date] >= _start )
&& OR ( [Retail Date] = BLANK (), [Retail Date] >= prevDate )
)
)
)
Regards,
Xiaoxin Sheng
Please download the .pbix from the following link
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
105 | |
99 | |
99 | |
38 | |
37 |
User | Count |
---|---|
157 | |
121 | |
73 | |
73 | |
63 |