topic Re: Cumulative sum zero values should display previous values till end values in DAX Commands and Tips

Cumulative sum zero values should display previous values till end values

vijaykumarj19 — Wed, 28 Oct 2020 07:55:53 GMT

c1	c2	c3
may 2020	32	32
jun 2020	22	54
jul 2020		54
aug 2020		54
sept 2020	34	88
oct 2020	2	90
nov 2020
dec 2020

I have c2 column and need to display c3 running total but blank values before last value should display previous values

my table is connected to date table

my measure:

CALCULATE(

sum(c2),

FILTER(

CALCULATETABLE(

SUMMARIZE(

'Date Table',

'Date Table'[cc_year-Month Sort],

'Date Table'[Year Month]

ALLSELECTED('Date Table')

ISONORAFTER(

'Date Table'[cc_year-Month Sort], MAX('Date Table'[cc_year-Month Sort]), DESC,

'Date Table'[Year Month], MAX('Date Table'[Year Month]), DESC

)

but displaying cumulative till last

Re: Cumulative sum zero values should display previous values till end values

wdx223_Daniel — Wed, 28 Oct 2020 08:42:04 GMT

vijaykumarj19 assume c1 from date table then try this code

=var _currentYM=max(datetable[year month]) return calculate(sum(c2),datetable[year month]<=_currentYM)

Re: Cumulative sum zero values should display previous values till end values

vijaykumarj19 — Wed, 28 Oct 2020 09:05:58 GMT

Its not showing cumulative count

Re: Cumulative sum zero values should display previous values till end values

vijaykumarj19 — Mon, 02 Nov 2020 00:21:56 GMT

My table

calculating cumulative and ploted in line graph

Below line should stop at a6

Dax :

c2 running total in c1 =

CALCULATE(

SUM('Table'[c2]),

FILTER(

ALLSELECTED('Table'[c1]),

ISONORAFTER('Table'[c1], MAX('Table'[c1]), DESC)

))

Re: Cumulative sum zero values should display previous values till end values

vijaykumarj19 — Mon, 02 Nov 2020 07:52:45 GMT

can anyone help me with the solution

Re: Cumulative sum zero values should display previous values till end values

wdx223_Daniel — Mon, 02 Nov 2020 08:09:00 GMT

vijaykumarj19

think you need a IF function

Re: Cumulative sum zero values should display previous values till end values

vijaykumarj19 — Mon, 02 Nov 2020 08:13:20 GMT

Need to show a3 and a4 with a2 value

Re: Cumulative sum zero values should display previous values till end values

wdx223_Daniel — Mon, 02 Nov 2020 08:30:19 GMT

vijaykumarj19

can this work?

Re: Cumulative sum zero values should display previous values till end values

vijaykumarj19 — Mon, 02 Nov 2020 12:40:59 GMT

If we have dates as axis

If i have month year from date table as axis then

and i do have a relation between tables

Then how would this dax modified

var c = MAX('Table'[c1])
VAR d = MAXX(FILTER(ALL('Table'),'Table'[c2]>0),'Table'[c1])
Return
IF(c<=d,
CALCULATE(SUM('Table'[c2]),FILTER(ALL('Table'),'Table'[c1]<=c)))

Re: Cumulative sum zero values should display previous values till end values

wdx223_Daniel — Tue, 03 Nov 2020 01:12:12 GMT

vijaykumarj19

AccTotalc2 = VAR vCurrentDate=MAX(DateTable[Date]) VAR vMaxDate=CALCULATE(MAX('Fact'[c1]),FILTER(ALL('Fact'),'Fact'[c2]>0)) RETURN IF(vCurrentDate<=vMaxDate,CALCULATE(SUM('Fact'[c2]),DateTable[Date]<=vCurrentDate))

AccTotalc4 = VAR vCurrentDate=MAX(DateTable[Date]) VAR vMaxDate=CALCULATE(MAX('Fact'[c1]),FILTER(ALL('Fact'),'Fact'[c4]>0)) RETURN IF(vCurrentDate<=vMaxDate,CALCULATE(SUM('Fact'[c4]),DateTable[Date]<=vCurrentDate))

Re: Cumulative sum zero values should display previous values till end values

vijaykumarj19 — Tue, 03 Nov 2020 03:54:17 GMT

But it stoped at dec need to be extended till Jan , Because we have value for Jan also

Re: Cumulative sum zero values should display previous values till end values

wdx223_Daniel — Tue, 03 Nov 2020 05:49:45 GMT

vijaykumarj19 try to change MAX to MIN

Re: Cumulative sum zero values should display previous values till end values

BhushanR — Mon, 12 Aug 2024 14:16:51 GMT

If tried same scenario and try to filter the data by using slicer which I created from date field it is not giving correct count.