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.
Super UserYou 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,
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.