Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Running total between two selected periods

Hello,

I have a fact table with a relationship to a calendar table marked as Date table. Relation ship is one to n based on a date column.

I can create time intelligence clculations like YTD, Previsous year, previos period but I cannot create a running total that would work between two dates, i.e. If I select March to October of one of the year in the table I want to see the cumulative total starting with March value and ending in October as a running total.

It will not work, I tried several calculations, it is just replicating the value for the line not calculating the running total.
Formula 1
PTD Amount USD:=CALCULATE (
SUM(Bol[AmountUSD]);
FILTER (
ALL('Sailing Date'[Sailing Date]) ;
'Sailing Date'[Sailing Date] >= MIN('Sailing Date'[Sailing Date] )&&
'Sailing Date'[Sailing Date] <= MAX ('Sailing Date'[Sailing Date])))

 

Formula 2
Cumulative = CALCULATE (
SUM(Bol[AmountUSD]),
FILTER (
ALLSELECTED( 'BOL' ),
'Bol'[Sailing Date] >=MIN ('Sailing Date'[Sailing Date])&&
'Bol'[Sailing Date] <= MAX ( 'Bol'[Sailing Date] )))

 

I am not sure where to look at to find what is preventing these formulas to work as expected.

Any idea is appreciated, Thanks

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    Hello,

    Thanks for all your suggestions. I finally found a way to calculate this running total using a formula like this one.

     

    Cumulative Dollar :=
IF (
    MIN ( 'Dim_Calendar'[ID_Date] )
        <= CALCULATE ( MAX ( BOL[Sailing_Date_Key] ), ALL ( BOL ) );
CALCULATE(SUM(Bol[AmountUSD]),
Filter(
ALLSELECTED('Dim_Calendar'),'Dim_Calendar'[DateASDate]<=MAX('Dim_Calendar'[DateASDate])))
)

    Which produces exactly the result I want

      YYYYWK  AmountUSD  Cumulative

    2018461'327'2431'327'243
    2018471'192'8902'520'134
    2018481'604'9164'125'049
    201849745'2564'870'306
    20185020'7924'891'098
    201851 4'891'098
    201852 4'891'098
    201901 4'891'098
    201902 4'891'098
    201903 4'891'098
    201904 4'891'098
    201905 4'891'098
    201906 4'891'098
    20190732'8344'923'932
    201908 4'923'932
    201909 4'923'932

7 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    7 years ago

    Hi Anonymous,

     

    Try this formula, please. The cause is the initial date is dynamic.

    Measure 7 =
VAR minDate =
    CALCULATE (
        MIN ( 'Sailing Date'[Sailing Date] ),
        ALLSELECTED ( 'Sailing Date'[Sailing Date] )
    )
RETURN
    CALCULATE (
        Bol[AmountUSD],
        FILTER (
            ALL ( 'Sailing Date'[Sailing Date] ),
            'Sailing Date'[Sailing Date] >= minDate
                && 'Sailing Date'[Sailing Date] <= MAX ( 'Sailing Date'[Sailing Date] )
        )
    )

    Running-total-between-two-selected-periods

     

     

    Best Regards,
    Dale

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hello,

      Thanks for all your suggestions. I finally found a way to calculate this running total using a formula like this one.

       

      Cumulative Dollar :=
IF (
    MIN ( 'Dim_Calendar'[ID_Date] )
        <= CALCULATE ( MAX ( BOL[Sailing_Date_Key] ), ALL ( BOL ) );
CALCULATE(SUM(Bol[AmountUSD]),
Filter(
ALLSELECTED('Dim_Calendar'),'Dim_Calendar'[DateASDate]<=MAX('Dim_Calendar'[DateASDate])))
)

      Which produces exactly the result I want

        YYYYWK  AmountUSD  Cumulative

      2018461'327'2431'327'243
      2018471'192'8902'520'134
      2018481'604'9164'125'049
      201849745'2564'870'306
      20185020'7924'891'098
      201851 4'891'098
      201852 4'891'098
      201901 4'891'098
      201902 4'891'098
      201903 4'891'098
      201904 4'891'098
      201905 4'891'098
      201906 4'891'098
      20190732'8344'923'932
      201908 4'923'932
      201909 4'923'932
      • renanfmacedo's avatar
        renanfmacedo
        Frequent Visitor
        3 years ago

        I had the same problem, it worked perfectly, thank you.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hello,

      I was able to get the running total calculation working. I can now select any time period and get the running total starting at the begining of the period.

      PTD Amount USD:=
IF (
    MIN ('Sailing Date'[date_key])
        <= CALCULATE ( MAX ( BOL[Sailing_Date_Key] ); ALL (Bol ) );
CALCULATE(SUM(Bol[AmountUSD]);
Filter(
ALLSELECTED('Sailing Date');'Sailing Date'[Sailing Date]<=MAX('Sailing Date'[Sailing Date])))
)

      The result is like that

       YYYYWK   AmountUSD   Cumulative

      2018461'327'2431'327'243
      2018471'192'8902'520'134
      2018481'604'9164'125'049
      201849745'2564'870'306
      20185020'7924'891'098
      201851 4'891'098
      20190732'8344'923'932
      201908 4'923'932
      201909 4'923'932
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      My running total should not have Running Total with date on rows. 

      I should use Month Year. Format "May 2019".

      As the solution use:

      ALLSELECTED ( 'Sailing Date'[Sailing Date] )

       ALL ( 'Sailing Date'[Sailing Date] ),

       

      I change to

      ALLSELECTED ( 'Sailing Date') Without the column.

      ALL ( 'Sailing Date') Without the column.

      So it doesn't matter which column from calender table Used on rows, the Running Total will Work.

       

      v-jiascu-msft/Dale , Thank you for your support and also Anonymous for creating the Post.

       

      Best Regards

      Amdi 

       

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        The code is:

        Measure 7.2 = 
VAR minDate =
    CALCULATE (
        MIN ( 'Date_2'[Date] );
        ALLSELECTED ( Date_2)
    )
RETURN
    CALCULATE (
        [Sales Amount];
        FILTER (
            ALL ('Date_2'  );
            'Date_2'[Date] >= minDate
                && 'Date_2'[Date] <= MAX ( 'Date_2'[Date] )
        )
    )

         

  • Omega's avatar
    Omega
    Icon for Impactful Individual rankImpactful Individual
    7 years ago

    Can you please share a sample data?