Forum Discussion

asyikin_mazelan's avatar
asyikin_mazelan
Regular Visitor
7 years ago
Solved

Add value in current year with value from upcoming year

Hello, I got problem to get value from future year to be calculate with cumulative value in current year.

I got a column (year) that have value of year such as 2000,2001...(etc) and another column (filled/vacant) which have value of "filled" or "vacant".

From that two column I do two measures to get how many filled(totalFilled) and how many vacant(totalVacant) in that column.
Next, I make another two measures to get the cumulative value of those measures (totalFilled and totalVacant). Lastly, I add those two cumulative measures to get Total Cumulative Value of All (TotalCumulative).

In Line and Stack column chart, I pick the two cumulative measures as the columns value and i choose column (year) as shared axis.

My question is how can I include the value from upcoming year to be added in the current year ?

Example, in 2019 there are 10 vacant position will be filled and 2020 there are 5 position will be filled.
So, I want cumulative value for 2019 be like this (cumulative value from previous year + 10 + 5).

Thus, any value from upcoming year will be added to current year. 

Hope I can get the solution. Thank you.

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    Hi asyikin_mazelan ,

    According to your description, it seems like a calculate for cumulative previous total and coming rolling total, right?

    If this is a case, you can try to use following measures if they works:

    Previous Rolling =
VAR currDate =
    MAX ( Table[date] )
RETURN
    CALCULATE (
        SUM ( Table[Amount] ),
        FILTER ( ALLSELECTED ( Table ), [Date] <= currDate ),
        VALUES ( Table[Category] )
    )

Coming Rolling =
VAR currDate =
    MAX ( Table[date] )
RETURN
    CALCULATE (
        SUM ( Table[Amount] ),
        FILTER ( ALLSELECTED ( Table ), [Date] >= currDate ),
        VALUES ( Table[Category] )
    )

    If above not help, can you please provide some sample data for test? It is hard to coding formula without any sample data.

    Regards,

    Xiaoxin Sheng

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi asyikin_mazelan ,

    According to your description, it seems like a calculate for cumulative previous total and coming rolling total, right?

    If this is a case, you can try to use following measures if they works:

    Previous Rolling =
VAR currDate =
    MAX ( Table[date] )
RETURN
    CALCULATE (
        SUM ( Table[Amount] ),
        FILTER ( ALLSELECTED ( Table ), [Date] <= currDate ),
        VALUES ( Table[Category] )
    )

Coming Rolling =
VAR currDate =
    MAX ( Table[date] )
RETURN
    CALCULATE (
        SUM ( Table[Amount] ),
        FILTER ( ALLSELECTED ( Table ), [Date] >= currDate ),
        VALUES ( Table[Category] )
    )

    If above not help, can you please provide some sample data for test? It is hard to coding formula without any sample data.

    Regards,

    Xiaoxin Sheng