"Inverse" Cumulative Sum

Tested already, but let me double check, thanks a lot.

working in the second option...

Thanks a lot Anonymous it work, need to do some adjustment of course, but it general it worked.

---

Anonymous wrote:

Hi CarlosR

 

It was challenging to get a solution for this.

 

I am assuming you have a calendar  / Date table based on the Date of your FactTable and the  two are linked.

 

If you do so, please follow the steps 

1. Create a column

                  Month_Start_Date = STARTOFMONTH(Calendar[Date]).

                  For each row it will be FirstDay of the corresponding month.

2. Create a column

                 MaxDate = Max(Calendar[Date])

                 This will be last date of your FactTable.

3.  Create Month_End_Date = IF ( EOMONTH('Calendar'[Date],0) >   'Calendar'[MaxDate], 

                                                        'Calendar'[MaxDate],EOMONTH('Calendar'[Date],0) )

     This crerates for eachrow the lastday of the corresponding month. If the month does not have all days in a month then it will be lastdate of the Date from the FactTable.

4. Create a column

                            UptoDate = DateDiff(Calendar[Date],'Calendar'[Month_End_Date],DAY) + 1

     For Each row it calculates the number of days beween the date and the Month_End_Date

5. Create a column

      GobackDate = Date(year(Calendar[Date]),Month(Calendar[Date]),Calendar[UptoDate])

6. Now the final measure

    ReverseTotal =
                          var BeginDate =MAX(Calendar[Month_Start_Date])
                           var EndDate = Max(Calendar[GobackDate])
                   return
                         sumx(    Filter(  ALL('Calendar'),Calendar[Date] >= BeginDate && 'Calendar'[Date] <= EndDate ),

                                            [SalesAmount] 
                                          )

     Here [SaleAmount] is a measure of total of Sales from the FactTable.

7.  Here is the output for Jan2015 where I have FactTable Records for the wholeMonth 1- 31 Jan 2015

   

8. Here is the output for Feb2015 where the data is only upto 10 Feb 2015

 

   

    

Try it out it should work wonders for you.

 

If it works please accpet this as solution and also give KUDOS.

 

Cheers

 

CheenuSing

 

 

 

---

Anonymous

Thanks for your support, now I need to make this column, the MaxDate, specific for a particular dimension, the current solution applies for the complete fact table.

MaxDate = Max(Calendar[Date])

Any idea? thanks