Forum Discussion

Ackbar-Learner's avatar
Ackbar-Learner
Icon for Resolver I rankResolver I
3 years ago
Solved

Help with DAX on running total

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!

 

Help!
  • Ackbar-Learner's avatar
    Ackbar-Learner
    3 years ago

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

3 Replies

  • Ackbar-Learner's avatar
    Ackbar-Learner
    Icon for Resolver I rankResolver I
    3 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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.

     

     

    • Ackbar-Learner's avatar
      Ackbar-Learner
      Icon for Resolver I rankResolver I
      3 years ago

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