Forum Discussion

heetu24's avatar
heetu24
Icon for Helper I rankHelper I
2 years ago
Solved

Cumulative sales

Hello, All I am stuck in one scenario.   Scenario: As per below screens shot. There is filter of Period and Year in Green color. In Power BI Period and Year are slicers. In Year column records a...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi All
    Fisrtly Irwan  thank you for your solution!
    And heetu24, We just need to make a slight change in Irwan's DAX, and the filtering of YEARS with the selectedvalue function can be done to accumulate different years.

    Measure = 
VAR A=SELECTEDVALUE('data'[Year])
VAR C=MAX(data[Index])
VAR B=CALCULATE(
    SUM('data'[Sales Value]),FILTER(ALL('data'),
   'data'[Year]=A&&'data'[Index]<=C)
    )
    RETURN
    IF(
        MAX('data'[Index])=1,
        0,
        B)

    If you have any other questions, check out the pbix file I uploaded, I hope it helps!

     

    Hope it helps!

     

    Best regards,
    Community Support Team_ Tom Shen

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

Irwan's avatar
Irwan
Icon for Super User rankSuper User
2 years ago

hello heetu24 

 

please check if this accomodate your need.

 

since i dont see any order in that sample data, i assumed you have another column for sorting or indexing cummulative in that excel calculation. So i created another column for indexing from power query (this should be adjusted to your order column).

 

create a new measure with following DAX 

Cummulative 1 = 
var _Index = SELECTEDVALUE('Table'[Index])
var _Sum =
CALCULATE(
    SUM('Table'[Sales Value]),
    FILTER(
        ALL('Table'),
        'Table'[Index]<=_Index
    )
)
Return
IF(
    _Index=1,
    0,
    _Sum
)
Cummulative 2 = 
var _Index = SELECTEDVALUE('Table'[Index])
var _Sum = 
SUMX(
    FILTER(
        ALL('Table'),
        'Table'[Index]<=_Index
    ),
    'Table'[Sales Value]
)
Return
IF(
    _Index=1,
    0,
    _Sum
)

both measure Cummulative 1 and Cummulative 2 have same result, but Cummulative 1 is using CALCULATE while Cummulative 2 is using SUMX. Choose either way should be fine.

 

Hope this will help you.

Thank you.

heetu24's avatar
heetu24
Icon for Helper I rankHelper I
2 years ago

Hi Thank you for this solution.

 

As I mentioned in screen shot. I am looking cumulative for year  wise that you provided cumulative for TY and same for YA and 2 YA .

So if user select year select on TY/YA /2YA any of this so dynamically it can change cumulative sales. Is it possible to use summarize DAX function?

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi All
    Fisrtly Irwan  thank you for your solution!
    And heetu24, We just need to make a slight change in Irwan's DAX, and the filtering of YEARS with the selectedvalue function can be done to accumulate different years.

    Measure = 
VAR A=SELECTEDVALUE('data'[Year])
VAR C=MAX(data[Index])
VAR B=CALCULATE(
    SUM('data'[Sales Value]),FILTER(ALL('data'),
   'data'[Year]=A&&'data'[Index]<=C)
    )
    RETURN
    IF(
        MAX('data'[Index])=1,
        0,
        B)

    If you have any other questions, check out the pbix file I uploaded, I hope it helps!

     

    Hope it helps!

     

    Best regards,
    Community Support Team_ Tom Shen

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