Forum Discussion

mbahonen's avatar
mbahonen
Icon for Advocate II rankAdvocate II
2 years ago

Trailing Months Cumulative Sum with Disconnected Date Table

Hello,

 

I posted a version of this an hour ago that was more detailed but it seems to have not saved (but if you see a version of it out there, let me know!)

I am trying to find a solution that modifies the trailing months DAX used in this link: Show previous 6 months of data from single slicer selection - SQLBI

 

Instead of the "SELECTEDMEASURE()" function, however, I would replaced with an established measure that works well without a disconnected date table and slicer for end of month. I would also modify from 6 months to 12 months. 

 

I have used the above formula many times to convert my non-dynamic values into dynamic trailing values. It works fine in my current project for basic monthly totals, however, I'm trying to find a solution for it to calculate Cumulative Sum. Nothing I have tried works including spending a day with ChatGPT. It will either still give me the monthly total, or does not limit the result to trailing 12 months, and gives the same value across all months. 

 

I need the result to cumulate the total for the past 12 months for each month listed in a matrix visualization. So if the user selects March 2024, I would want the monthly total for March 2024 plus all months back to April 2023.  If the user selects January 2024, it should be the cumulative total back to Feb 2023.  But it should also show the cumulative total for the Dec 2023, Nov 2023, etc in the matrix even though you won't see all 12 months prior for all months displayed.

 

Sorry, I had a visual version of this to explain better, but apparently it was deleted. If this doesn't make sense I can try to replicate tomorrow, but wanted to post this in case anyone has some good ideas overnight (here).

 

ETA: I added some mock up data and more context in a comment below.  


Thanks!

Need Help

6 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    2 years ago

    Hi,

    Share data in a format that can be pasted in an MS Excel file and show the expected result.

    • mbahonen's avatar
      mbahonen
      Icon for Advocate II rankAdvocate II
      2 years ago

      Hi, yes, sorry. As mentioned my first post with that was apparently not posted, and I had deleted my mock up. Here is a sample of what I'm looking for.

       

      I need a dynamic rolling measure ideally similar to the one in the link of my OP with a disconnected date table and a user impacted on page slicer to calculate the 12M Cumulative Total. Right now the results I'm getting are just the Total. I've tried about 10 variations. Nothing seems to take the 12M history cumulative total into account. The visualization (matrix table but could be bar chart, etc) will display 12 only months with the latest date the user selected date (month year), and the earliest date 12 months earlier, but the data needs to pull from as much as is available on a rolling 12M basis. 

       

      MonthYearTotal12M Cumulative Total
      March202450440
      Feb202445435
      Jan202460430
      Dec202320425
      Nov202330420
      Oct202315415
      Sep202375410
      Aug20235405
      Jul202335400
      Jun202340395
      May202310390
      Apr202355385
      March202345340
      Feb20234012M history not availble
      Jan20235512M history not availble
      Dec20221512M history not availble
      Nov20222512M history not availble
      Oct20221012M history not availble
      Sep20227012M history not availble
      Aug2022012M history not availble
      Jul20223012M history not availble
      Jun20223512M history not availble
      May2022512M history not availble
      Apr20221012M history not availble
        End of Known Data 
      • Ashish_Mathur's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        2 years ago

        I am just unable to understand your question.  Do not point to another link please.  Just explain the question is simple English.

  • mbahonen's avatar
    mbahonen
    Icon for Advocate II rankAdvocate II
    2 years ago

    More context: 

    Here is my original measure and the new measure that is the most promising so far, but that does not yet give the cumulative total, only the individual total (this is about the 10th version I've tried):

     

    Original:

    12M Cumulative Sum =

    VAR StartDate =

        NEXTMONTH ( LASTDATE ( DATEADD ( 'FactTable’[ReportDate], -12, MONTH ) ) )

    VAR EndDate =

        LASTDATE ( 'FactTable’[ReportDate])

    RETURN

        CALCULATE (

            SUM ( FactTable[RecordCount] ),

            DATESBETWEEN ( 'FactTable’[ReportDate], StartDate, EndDate )

        )

     

    Current Front-runner dynamic wrapper:

    12M Cumulative Sum LOOKBACK WIP 2 - DOES NOT WORK =

    VAR StartDate =

        NEXTMONTH ( LASTDATE ( DATEADD ( 'FactTable’[ReportDate], -12, MONTH ) ) )

    VAR EndDate =

        LASTDATE ( FactTable[ReportDate])

     

    VAR Results =

    CALCULATE( SUMX(FactTable, FactTable[RecordCount] ),

            DATESBETWEEN ( FactTable[ReportDate], StartDate, EndDate ), TREATAS( VALUES('Calendar LOOKBACK'[Date]), 'Calendar'[Date] ))

    RETURN

    Results

  • mbahonen's avatar
    mbahonen
    Icon for Advocate II rankAdvocate II
    2 years ago

    Here is some more context and info for what I've tried so far.

     

    This is my original Non-Dynamic measure that accurately gives me the cumulative 12M sum (but does not work with user interactive slicers):

     

    12M Cumulative Sum =

    VAR StartDate =

        NEXTMONTH ( LASTDATE ( DATEADD ( 'FactTable’[ReportDate], -12, MONTH ) ) )

    VAR EndDate =

        LASTDATE ( 'FactTable’[ReportDate])

    RETURN

        CALCULATE (

            SUM ( FactTable[RecordCount] ),

            DATESBETWEEN ( 'FactTable’[ReportDate], StartDate, EndDate )

        )

     

    Attempt for Rolling Measure 1: Dynamic Rolling 12M Wrapper (DOES NOT WORK)

    • I have used this version for simpler measure many times and it works with other measure in my current report that only care about individual monthly totals. 
    • Does not work with for [12M Cumulative Sum]; gives a weird cumulative total I haven’t figured out, but it does “wrap dynamically” in that it limits the values to the 12M rolling period
    • It also gives the correctly monthly Total (see sample data) value in the dynamic maxtrix (BUT does not correctly give the cumulative sum in the non-dynamic matrix per previous bullet)

     

    12M Cumulative Sum LOOKBACK WIP 1 =

    VAR ReferenceDate = MAX ('Calendar'[Date])

    VAR PreviousDate =

        DATESINPERIOD(

            'Calendar LOOKBACK'[Date],

            ReferenceDate,

            -12,

            MONTH

        )

    VAR Results =

        CALCULATE (

            [12M Cumulative Sum],

            REMOVEFILTERS('Calendar'),

            KEEPFILTERS(PreviousDate),

            USERELATIONSHIP('Calendar'[Date], 'Calendar LOOKBACK'[Date])

        )

     

    RETURN

        Results

     

    Attempt for Rolling Measure 2:  Dynamic Rolling 12M Wrapper (PARTIALLY WORKS)

    • This version same version I found in the link referenced below. It’s the ONLY version I’ve tried in the last day that gives the correct cumulative value in the non-dynamic table, but still only gives the monthly total in the dynamic table. I think this is on the right track but still doesn’t give cumulative totals in the dynamic version, only the original non-dynamic version of the visualization. In the dynamic matrix it will just show the individual month totals (like so many of the other versions I've tried). 

     

    12M Cumulative Sum LOOKBACK WIP 2 =

    VAR StartDate =

        NEXTMONTH ( LASTDATE ( DATEADD ( 'FactTable’[ReportDate], -12, MONTH ) ) )

    VAR EndDate =

        LASTDATE ( FactTable[ReportDate])

     

    VAR Results =

    CALCULATE( SUMX(FactTable, FactTable[RecordCount] ),

            DATESBETWEEN ( FactTable[ReportDate], StartDate, EndDate ), TREATAS( VALUES('Calendar LOOKBACK'[Date]), 'Calendar'[Date] ))

    RETURN

    Results

     

    // REF: https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Slice-value-based-on-disconnected-table-with-dynamic-values/m-p/2740064