Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi
So i got two measures that show the actual count per product and another that show the cumulative count per product:
What i need is a DAX formula that shows the Inventory RT data for all lines that are representing the product cumulative subtotals and the RevenueDataAllLines data for lines named Inventory in and Units sold.
The formula for RevenueDataAllLines is =sum(RevenueInputsDatabase[Value])
The formula for Inventory RT is
=VAR LastVisibleDate =
MAX ( BudgetCalendar[Date] )
VAR FirstVisibleDate =
MIN ( BudgetCalendar[Date] )
VAR LastDateWithSales =
CALCULATE (
MAX ( RevenueInputsDatabase[Transaction Date] ),
ALL(RevenueInputsDatabase)
)
VAR Result =
IF (
FirstVisibleDate <= LastDateWithSales,
CALCULATE (
[RevenueDataAllLines],
BudgetCalendar[Date]<= LastVisibleDate
)
)
RETURN
Result
Below is the result that should be displayed:
Thanks in advance!
Solved! Go to Solution.
I used HASONEVALUE instead of isinscope! Sorry for not formatting properly the code.
ProjectedInventory:=VAR Result = [InventoryRT]
RETURN
if(if(HASONEVALUE(RevenueInputsDatabase[AccountGL]),values(RevenueInputsDatabase[AccountGL]))="Inventory in", [RevenueDataAllLines],if(if(HASONEVALUE(RevenueInputsDatabase[AccountGL]),values(RevenueInputsDatabase[AccountGL]))="Units sold", [RevenueDataAllLines],Result))
I used HASONEVALUE instead of isinscope! Sorry for not formatting properly the code.
ProjectedInventory:=VAR Result = [InventoryRT]
RETURN
if(if(HASONEVALUE(RevenueInputsDatabase[AccountGL]),values(RevenueInputsDatabase[AccountGL]))="Inventory in", [RevenueDataAllLines],if(if(HASONEVALUE(RevenueInputsDatabase[AccountGL]),values(RevenueInputsDatabase[AccountGL]))="Units sold", [RevenueDataAllLines],Result))
Hi @Ackbar-Learner ,
According to your statement, I think your add [Stock Management] in Matrix Rows Fields as level1 and [Level2] column which contains data like "Inventory in" ,"Units sold" ... in Matrix Rows Fields as level2. Due to I don't know the name of [Level2], so I name it as [Level2]. Now you want to show running total in level1 and sum in level2.
Here I suggest you to try ISINSCOPE() to create a new measure based on [Inventory RT] and [RevenueDataAllLines].
New Inventory RT =
IF (
ISINSCOPE ( [Level2] ),
[RevenueDataAllLines],
IF ( ISINSCOPE ( [Stock Management] ), [Inventory RT] )
)
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@v-rzhou-msft I forgot to mention this is in powerpivot and ISINSCOPE cannot be used in powerpivot 🙁
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
24 | |
20 | |
15 | |
13 |
User | Count |
---|---|
51 | |
42 | |
39 | |
19 | |
19 |