Forum Discussion
Cumulative Sum - Blank Values
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 ) )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.
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.
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
Super User
Yes, you should be able to pull your FY column from the date table and it will still sum correctly.
- jdbuchanan71
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.