Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
CarlosR
Frequent Visitor

"Inverse" Cumulative Sum

Hi,

 

I wonder to know to create this "inverse" cumulative sum.

 

As a reference, I already took this link already, it works...

cumulative for certain period

 

But instead of start with the value of the first period, I want to start from the total and subtracting the value of the current period until cero

 

Date   Value  "Inverse"Cumulative   "Normal"Cumulative

Day0    0       12                                      0

Day1    2       10                                      2

Day2    4       6                                        6

Day3    6       0                                       12

 

 

Thanks in advance

1 ACCEPTED SOLUTION
CheenuSing
Community Champion
Community Champion

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

   Capture.GIF

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

 

   Capture.GIF

    

Try it out it should work wonders for you.

 

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

 

Cheers

 

CheenuSing

 

 

 

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

View solution in original post

7 REPLIES 7
CheenuSing
Community Champion
Community Champion

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

   Capture.GIF

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

 

   Capture.GIF

    

Try it out it should work wonders for you.

 

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

 

Cheers

 

CheenuSing

 

 

 

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

@CheenuSing

 

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

I found it

 

MaxDate = calCULATE(Max(table[Date]),VALUES(table[Column])

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


@CheenuSing 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

   Capture.GIF

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

 

   Capture.GIF

    

Try it out it should work wonders for you.

 

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

 

Cheers

 

CheenuSing

 

 

 


 

working in the second option...

ImkeF
Super User
Super User

changing the condition like this should work:

 

Cumulative 2 = CALCULATE (
    SUM ( 'Invoice lines'[Sales] ),
    FILTER (
        ALLSELECTED(BI-Dates),
        'BI-Dates'[Date] >= MIN ( 'BI-Dates'[Date] )
    )
)

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

CarlosR
Frequent Visitor

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.