Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Cumulative Sum - Blank Values

I'm using a matrix to represent the increase of the numbers of users between the fiscal years. In my matrix, if the year don't have users added the sum is bring a blank value. 

 

Here is the formula that I'm using: Measure = CALCULATE(
SUM('vw_qtd_usuarios_ano_departamento_acm'[NumberOfUsers]);
FILTER(all('vw_qtd_usuarios_ano_departamento_acm'[FY]);'vw_qtd_usuarios_ano_departamento_acm'[FY]<=max('vw_qtd_usuarios_ano_departamento_acm'[FY])))

 

Can someone help me?

 

Need Help
  • jdbuchanan71's avatar
    jdbuchanan71
    7 years ago

    I was not able to get this to work without a separate date table.  If I have a date table with a FY column then I was able to use the following measure to get the amounts to roll forward.

     

    Running Total = 
VAR MaxDate = MAX( 'Date'[FY] )
RETURN
CALCULATE(
    [Total Revenue]
    ,FILTER ( ALL ('Date') , 'Date'[FY] <= MaxDate ) )

     

     

  • jdbuchanan71's avatar
    jdbuchanan71
    7 years ago

    You won't need a different formula, you would just need to pull the FiscalYear field into the columns of the matrix.  The way the formula works is by figuring out the highest date that is represented in the column (or row) and summing everything up to that date.   As an example, I wrote the first part of out measure into my date table and I also set a FY starting in Oct.

     

    If I pull Calender year into the columns the highest date I get is Dec 31st.

     

    If I switch my columns to be my FY field from my date table the highest date in any FY is 9/30.  Because the rest of the measure uses that date it will only calc up through that date if I put FY in the columns.

     

     

7 Replies

  • jdbuchanan71's avatar
    jdbuchanan71
    Super User
    7 years ago

    I was not able to get this to work without a separate date table.  If I have a date table with a FY column then I was able to use the following measure to get the amounts to roll forward.

     

    Running Total = 
VAR MaxDate = MAX( 'Date'[FY] )
RETURN
CALCULATE(
    [Total Revenue]
    ,FILTER ( ALL ('Date') , 'Date'[FY] <= MaxDate ) )

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi jdbuchanan71, Thank you for reply my doubt.

       

      I can change the query to return the date and I will do a new test as you said in your example.

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        jdbuchanan71,

         

        Finally I got the result that I needing, but in this situation I'm doing a sum in the year, so can I sum based on fiscal year?

        Our fiscal year start on october and ends on september .

         

        QtyOfUsers =
        VAR MaxDate = MAX( 'vw_qtd_usuarios_ano_departamento_acm2'[Date_of_Register].[Date])
        RETURN
        CALCULATE(
        [SumofUsers]
        ;FILTER ( ALL ('vw_qtd_usuarios_ano_departamento_acm2'[Date_of_Register].[Date]) ; ' vw_qtd_usuarios_ano_departamento_acm2'[Date_of_Register].[Date] <= MaxDate ) )

         

         

         

        Thank you very much for your help.