Running total

[tanct]

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
    CurrentDay = MAX ( 'Data Source'[Weekday] )
 RETURN CALCULATE (
  SUMX ( VALUES ( 'Data Source'[Weekday] ), sum('Data Source'[Standard Quantity KG 3rd Party]) ),
  'Data Source'[Weekday] <= CurrentDay
 )

First time I posted my question here, thanks for your kind assistance.

Unable to send the BI file over here, do let me know should you need it.

Thanks!!

[Zubair_Muhammad]

@tanct

Try this Measure

Cumulative =
IF (
    HASONEVALUE ( 'Data Source'[Weekday] ),
    SUMX (
        FILTER (
            ALL ( 'Data Source' ),
            'Data Source'[Weekday] <= VALUES ( 'Data Source'[Weekday] )
        ),
        'Data Source'[Standard Quantity KG 3rd Party]
    )
)

[tanct]

Dear Zubair,

It is still not working. This is my first time publish my issue here, do you know how to share the pbix file over here, it would be more efficient in this way.

[Zubair_Muhammad]

Hi @tanct

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

[tanct]

https://drive.google.com/file/d/0B_Wuqq42mXSEcVNJalBydGFyQmM/view?usp=sharing

Many thanks for your reply and prompt reply, could you please click on the link for PBIX download for your shared expertise.

Thanks@!

[Zubair_Muhammad · Accepted solution]

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]

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

[tanct]

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

Strongly recommend !