Hi Folks, I'm trying to get the running total for my tables with date and group filters. Following is the DAX syntax I'm using but it is not giving the correct anwer,For Column, Running_TotalC = CALCULATE(SUM(Data_2[P1Log_Ret]),FILTER(ALLEXCEPT(Data_2, Data_2[Group] ,Data_2[ReturnDate]),Data_2[ReturnDate] <= EARLIER(Data_2[ReturnDate]))) And For measure, Running_Total = CALCULATE(SUM(Data_2[P1Log_Ret]),FILTER(ALLEXCEPT(Data_2, Data_2[PMSAcronym] ,Data_2[ReturnDate]),Data_2[ReturnDate] <= SELECTEDVALUE(Data_2[ReturnDate]))) Could you please help me in this. ReturnDateGroupFundReturnP1Log_RetRunning_TotalC1/31/2013 0:00A-0.00195227-0.001954178-0.0019541782/1/2013 0:00A0.0002673380.0002673020.0002673022/2/2013 0:00A0.0001860130.0001859960.0001859962/3/2013 0:00A0.0001866610.0001866440.0001866442/4/2013 0:00A-0.001295777-0.001296618-0.0012966182/5/2013 0:00A-0.000439921-0.000440018-0.0004400182/6/2013 0:00A0.000161580.0001615660.0001615662/7/2013 0:00A-0.000492769-0.00049289-0.000492892/8/2013 0:00A0.0005908210.0005906460.0005906462/9/2013 0:00A0.0001882430.0001882250.0001882252/10/2013 0:00A0.0001882080.0001881910.0001881912/11/2013 0:00A-0.000108518-0.000108524-0.0001085242/12/2013 0:00A3.2959E-063.29589E-063.29589E-062/1/2013 0:00B-0.001927674-0.001929534-0.0019295342/2/2013 0:00B0.0001794520.0001794360.0001794362/3/2013 0:00B0.0001804870.0001804710.0001804712/4/2013 0:00B0.0018849930.0018832190.0018832192/5/2013 0:00B-0.002777571-0.002781436-0.0027814362/6/2013 0:00B0.0031227460.003117880.003117882/7/2013 0:00B0.0004238510.0004237610.0004237612/8/2013 0:00B-0.000412682-0.000412767-0.000412767

Anonymous

Not applicable

6 years ago

Solved

Running Total Not Working

Hi Folks,

I'm trying to get the running total for my tables with date and group filters.

Following is the DAX syntax I'm using but it is not giving the correct anwer,

For Column,

Running_TotalC =

CALCULATE(

SUM(Data_2[P1Log_Ret]),

FILTER(

ALLEXCEPT(Data_2, Data_2[Group] ,Data_2[ReturnDate]),

Data_2[ReturnDate] <= EARLIER(Data_2[ReturnDate])

)

And For measure,

Running_Total =

CALCULATE(

SUM(Data_2[P1Log_Ret]),

FILTER(

ALLEXCEPT(Data_2, Data_2[PMSAcronym] ,Data_2[ReturnDate]),

Data_2[ReturnDate] <= SELECTEDVALUE(Data_2[ReturnDate])

)

Could you please help me in this.

ReturnDate	Group	FundReturn	P1Log_Ret	Running_TotalC
1/31/2013 0:00	A	-0.00195227	-0.001954178	-0.001954178
2/1/2013 0:00	A	0.000267338	0.000267302	0.000267302
2/2/2013 0:00	A	0.000186013	0.000185996	0.000185996
2/3/2013 0:00	A	0.000186661	0.000186644	0.000186644
2/4/2013 0:00	A	-0.001295777	-0.001296618	-0.001296618
2/5/2013 0:00	A	-0.000439921	-0.000440018	-0.000440018
2/6/2013 0:00	A	0.00016158	0.000161566	0.000161566
2/7/2013 0:00	A	-0.000492769	-0.00049289	-0.00049289
2/8/2013 0:00	A	0.000590821	0.000590646	0.000590646
2/9/2013 0:00	A	0.000188243	0.000188225	0.000188225
2/10/2013 0:00	A	0.000188208	0.000188191	0.000188191
2/11/2013 0:00	A	-0.000108518	-0.000108524	-0.000108524
2/12/2013 0:00	A	3.2959E-06	3.29589E-06	3.29589E-06
2/1/2013 0:00	B	-0.001927674	-0.001929534	-0.001929534
2/2/2013 0:00	B	0.000179452	0.000179436	0.000179436
2/3/2013 0:00	B	0.000180487	0.000180471	0.000180471
2/4/2013 0:00	B	0.001884993	0.001883219	0.001883219
2/5/2013 0:00	B	-0.002777571	-0.002781436	-0.002781436
2/6/2013 0:00	B	0.003122746	0.00311788	0.00311788
2/7/2013 0:00	B	0.000423851	0.000423761	0.000423761
2/8/2013 0:00	B	-0.000412682	-0.000412767	-0.000412767

Need Help

Tips and Tricks

Anonymous

6 years ago

Hi Anonymous ,

If i understand you correctly, please modify "ALLEXCEPT(Data_2, Data_2[Group] ,Data_2[ReturnDate])" to "ALLEXCEPT(Data_2, Data_2[Group])".

For Column:

Running_Total_C = 
CALCULATE (
    SUM ( Data_2[P1Log_Ret] ),
    FILTER (
        ALLEXCEPT ( Data_2, Data_2[Group] ),
        Data_2[ReturnDate] <= EARLIER ( Data_2[ReturnDate] )
    )
)

For Measure:

Running_Total_M = 
CALCULATE (
    SUM ( Data_2[P1Log_Ret] ),
    FILTER (
        ALLEXCEPT ( Data_2, Data_2[Group] ),
        Data_2[ReturnDate] <= SELECTEDVALUE ( Data_2[ReturnDate] )
    )
)

Result would be shown as below:

BTW, if you want to calculate Running Total per month, please refer to the formula below.

Running_Total_monthly = 
CALCULATE (
    SUM ( Data_2[P1Log_Ret] ),
    FILTER (
        ALLEXCEPT ( Data_2, Data_2[Group] ),
        Data_2[ReturnDate] <= EARLIER ( Data_2[ReturnDate] )
            && FORMAT ( Data_2[ReturnDate], "YYYYMM" )
                = FORMAT ( EARLIER ( Data_2[ReturnDate] ), "YYYYMM" )
    )
)

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

mwegener
Most Valuable Professional
6 years ago
Hi Anonymous

You may download my PBIX file from here.
Hope this helps.

If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.
mwegener
Most Valuable Professional
6 years ago
Hi Anonymous ,

did you solve your problem?

If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.

Anonymous

Not applicable

6 years ago

Hi Anonymous ,

If i understand you correctly, please modify "ALLEXCEPT(Data_2, Data_2[Group] ,Data_2[ReturnDate])" to "ALLEXCEPT(Data_2, Data_2[Group])".

For Column:

Running_Total_C = 
CALCULATE (
    SUM ( Data_2[P1Log_Ret] ),
    FILTER (
        ALLEXCEPT ( Data_2, Data_2[Group] ),
        Data_2[ReturnDate] <= EARLIER ( Data_2[ReturnDate] )
    )
)

For Measure:

Running_Total_M = 
CALCULATE (
    SUM ( Data_2[P1Log_Ret] ),
    FILTER (
        ALLEXCEPT ( Data_2, Data_2[Group] ),
        Data_2[ReturnDate] <= SELECTEDVALUE ( Data_2[ReturnDate] )
    )
)

Result would be shown as below:

BTW, if you want to calculate Running Total per month, please refer to the formula below.

Running_Total_monthly = 
CALCULATE (
    SUM ( Data_2[P1Log_Ret] ),
    FILTER (
        ALLEXCEPT ( Data_2, Data_2[Group] ),
        Data_2[ReturnDate] <= EARLIER ( Data_2[ReturnDate] )
            && FORMAT ( Data_2[ReturnDate], "YYYYMM" )
                = FORMAT ( EARLIER ( Data_2[ReturnDate] ), "YYYYMM" )
    )
)

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Forum Discussion

Running Total Not Working

3 Replies

Related content

Running Totals

Running total

Waterfall Charts with running totals

Tableau to PowerBI Running Totals Issue

SumX? - cumulative running total