Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

MTD Running Total Sales

Hi all,   I have already posted regarding this problem and never really found a solution so I am going to rephrase hoping someone out there can help.   I am trying to build measures allowing to g...
Need Help
  • popov's avatar
    popov
    8 years ago

    Correct formula

     

    Prev MTD Running Total Sales =
    VAR CurrentDayNumber =
        MIN ( MAX ( YTDInfos[CurrentDayNumber] ); MAX ( 'Calendar'[DayNumber] ) )
    RETURN
        IF (
            [Prev MTD Sales] <> BLANK ();
            CALCULATE (
                [Total Sales];
                FILTER (
                    ALL ( 'Calendar' );
                    AND ( 'Calendar'[MonthOffset] = -12; 'Calendar'[DayNumber] <= CurrentDayNumber )
                )
            )
        )

Anonymous's avatar
Anonymous
Not applicable
8 years ago

The expected behavior is actually the following...

 

 

Sorry for the inconvenience.

v-ljerr-msft's avatar
v-ljerr-msft
Microsoft Employee
8 years ago

Hi Anonymous,

 

Could you share a sample pbix file which can reproduce the issue, so that we can do some tests on it and better assist on this issue? You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading. :smileyhappy:

 

Regards

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Below a link to a pbix file illustrating the problem.

     

    RunningTotalProblemSample.pbix

     

    Thanks.

    • popov's avatar
      popov
      Resolver III
      8 years ago

      Hello!
      Try this formulas:

       

      MTD Running Total Sales =
      IF (
          [MTD Sales] <> BLANK ();
          CALCULATE (
              [Total Sales];
              FILTER (
                  ALL ( 'Calendar' );
                  'Calendar'[MonthOffset] = 0
                      && 'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
              )
          )
      )

       

       

      Prev MTD Running Total Sales =
      VAR CurrentDayNumber =
          MAX ( YTDInfos[CurrentDayNumber] )
      RETURN
          IF (
              [Prev MTD Sales] <> BLANK ();
              CALCULATE (
                  [Total Sales];
                  FILTER (
                      ALL ( 'Calendar' );
                      AND ( 'Calendar'[MonthOffset] = -12; 'Calendar'[DayNumber] <= CurrentDayNumber )
                  )
              )
          )

      • popov's avatar
        popov
        Resolver III
        8 years ago

        Sorry, Prev MTD Running Total Sales is not correct. I will post correct formula later