topic Re: Help with DAX on running total in DAX Commands and Tips

Help with DAX on running total

Ackbar-Learner — Tue, 08 Nov 2022 12:24:05 GMT

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!

Re: Help with DAX on running total

Anonymous — Wed, 09 Nov 2022 03:08:22 GMT

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.

Re: Help with DAX on running total

Ackbar-Learner — Wed, 09 Nov 2022 06:57:29 GMT

Anonymous I forgot to mention this is in powerpivot and ISINSCOPE cannot be used in powerpivot 🙁

Re: Help with DAX on running total

Ackbar-Learner — Mon, 14 Nov 2022 09:43:13 GMT

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))