Forum Discussion
Anonymous
6 years agoNot applicable
Enable future date in Reporting with Cummulative Sum
Hi All, My sales table has data from Jan till May. In the report, I want to show cummulative sum on Sales quantity. Future Months. i.e June till Dec , should also show last cummulative data until...
SQLbyoBI
Advocate I
6 years agoCan we create a Measure for Future Month, with last cummulative sum
The YTD measure below (SalesAmtYTD STAGE) will show cummulative sum through the end of the current year (by default) even if there aren't any sales transactions in the last few months of the year (e.g. Nov, Dec).
SalesAmtYTD STAGE =
CALCULATE(
SUM( 'Sales'[Amount] ),
DATESYTD( 'Date'['Date'] )
)
Or, if you want the cummulative sum for the last period with data to run through the end of time, you can do something like below...
SalesAmtYTD =
VAR __last_yrmth_with_data =
CALCULATE(
MAX( 'Date'[YearMonthNumber] ),
SUMMARIZE(
ALL('Sales'),
'Date'[YearMonthNumber]
)
)
VAR __current_yrmth =
SELECTEDVALUE( 'Date'[YearMonthNumber], MAX( 'Date'[YearMonthNumber] )
RETURN
IF(
__current_yrmth > __last_yrmth_with_data,
CALCULATE(
[SalesAmtYTD STAGE],
FILTER(
ALL( 'Date' ),
'Date'[YearMonthNumber] = __last_yrmth_with_data
)
),
[SalesAmtYTD STAGE]
)
How can we do without creating a seperate Date Table .
No, don't force it!