Forum Discussion

vijaykumarj19's avatar
vijaykumarj19
Icon for Microsoft Employee rankMicrosoft Employee
5 years ago
Solved

Cumulative sum zero values should display previous values till end values

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 nee...
cummulative dax
vijaykumarj19's avatar
vijaykumarj19
Icon for Microsoft Employee rankMicrosoft Employee
5 years ago

Need to show a3 and a4 with a2 value

  • vijaykumarj19's avatar
    vijaykumarj19
    Icon for Microsoft Employee rankMicrosoft Employee
    5 years ago

    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)))

     

     

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Icon for Community Champion rankCommunity Champion
    5 years ago

    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))
  • vijaykumarj19's avatar
    vijaykumarj19
    Icon for Microsoft Employee rankMicrosoft Employee
    5 years ago

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