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, Previ...
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
v-jiascu-msft's avatar
v-jiascu-msft
Microsoft 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] )
        )
    )