Enable future date in Reporting with Cummulative Sum

[Anonymous]

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

Since my Sales table does not have future month, how can I create a Calculated Column in sales table with Cummulative Sum until Dec.

OR 

Can we create a Measure for Future Month, with last cummulative sum 

What I have tried:

I have  created a seperate Date Table for future date and merged the tables for future month. 

How can we do without creating a seperate Date Table . 

Thanks a lot for your help and guidance

[v-kelly-msft]

Hi @Anonymous ，

First go to "edit queries">"Add column">"Index Column">"From 1":

Then go to "data view">"Modeling">"New column",adding a dax expression as below:

Column = CALCULATE(SUM('Table'[Value]),FILTER('Table','Table'[Index]<=EARLIER('Table'[Index])))

Finally you will see :

For the related .pbix file,pls click here  .

Best Regards,
Kelly

[Anonymous]

@v-kelly-msft @amitchandak @SQLbyoBI 

Thanks every one it was informative replies. The easiest solution for me was to create a seperate Date Table and then make a JOIN. Althought, this was quite slow in my power bi desktop, so I preferred to JOIN in SQL Database. 

[amitchandak]

I did not get all of it. But if you want to get cumulative from Jan to Dec, even if a few months are not there or Jun to May, You can use datesytd or toyalytd, both allow you to specify the end of the year.

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"5/31"))

Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"5/31"))
Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))

Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
2 Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(sales,sales[date] <=maxx(date,date[date])))

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-12,MONTH))  
Rolling last 12 before 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(dateadd('Date'[Date],-12,MONTH)),-12,MONTH))  

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s.

Refer
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi

https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[SQLbyoBI]

---

Can 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!