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

Hi tanct

 

You can upload your file to onedrive or google drive and share the link here using "insert/edit link" option

  • 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] ) )
)
    • tanct's avatar
      tanct
      Regular Visitor
      8 years ago

      Zubair, thanks so much for your prompt support, it was resolved less than few hours after I posted my issue here.

      Strongly recommend !

       

    • 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