Cumulative Sum - Blank Values

[Anonymous]

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?

[jdbuchanan71 · Accepted solution]

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 · Accepted solution]

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]

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]

@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.

[jdbuchanan71]

Yes, you should be able to pull your FY column from the date table and it will still sum correctly.

[Anonymous]

@jdbuchanan71,

There is a formula to calculate? How I can calculate it?

[jdbuchanan71 · Accepted solution]

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.

[Anonymous]

Hi @jdbuchanan71,

As you told me about the idea above, I created a field in my query on database that check if the month of the date is greater than 10, if yes, I add 120 days to input this data in the next year.

In this report I need the information by FY and does not import how is the new data and worked very well.

Thank you very much for your help.