Forum Discussion

tanct's avatar
tanct
Regular Visitor
8 years ago
Solved

Running total

  Hello All, Would need your help in the DAX running total for the below scenario (running total for the MTD - month to date and PM - previous month):-     My Dax: RunningTotal =  VAR  ...
Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    Hello friend

    tanct

     

    This shall do it

     

    Cumulative  =
SUMX (
    FILTER (
        ALLEXCEPT (
            'Data Source',
            'Data Source'[Reformat],
            'Data Source'[SH - Country  docum.]
        ),
        'Data Source'[Weekday] <= SELECTEDVALUE ( 'Data Source'[Weekday] )
    ),
    CALCULATE ( SUM ( 'Data Source'[Standard Quantity KG 3rd Party] ) )
)
Zubair_Muhammad's avatar
Zubair_Muhammad
Community Champion
8 years ago

Hello friend

tanct

 

This shall do it

 

Cumulative  =
SUMX (
    FILTER (
        ALLEXCEPT (
            'Data Source',
            'Data Source'[Reformat],
            'Data Source'[SH - Country  docum.]
        ),
        'Data Source'[Weekday] <= SELECTEDVALUE ( 'Data Source'[Weekday] )
    ),
    CALCULATE ( SUM ( 'Data Source'[Standard Quantity KG 3rd Party] ) )
)
v-piga-msft's avatar
v-piga-msft
Resident Rockstar
7 years ago

Hi estewart,

 

Sorry for the delay.

 

To achieve your requirement, you could try the steps below.

 

1. Duplicate the table in Query Editor and filter the row to only show the records like below. 

 

2.Then Apply and Close,  create the Index column for the two tables with IF function in Data View.

Index = IF('Table1'[Variable] in {"Trend_1","Trend_1_Start"},1,2)

Index = IF('Table2'[Variable] = "Trend_1_Start",1,2)

3. Create the lookupvalue calculated column in Table1. 

lookupvalue =
LOOKUPVALUE (
    'Table2'[Value],
    'Table2'[YearQuarter], 'Table1'[YearQuarter],
    Table2[Index], 'Table1'[Index]
)

Here is the output.

 

 

More details, please refer to the attachment.

 

Best  Regard,

Cherry