Forum Discussion

azeemnazim's avatar
azeemnazim
Helper II
6 years ago
Solved

Sum coming up wrong

Hi, I am using a date slicer on a stacked column chart but with item code and Net total amount, but on the date between 31/1/2019 the values are coming wrong for item code A and D. It is to be noted...
  • mahoneypat's avatar
    mahoneypat
    6 years ago

    I'm not sure exactly what you're looking for, so two options below.

    If you really want a measure with a fixed date filter, you can use this.

    Fixed sum due date ?
    CALCULATE (
    SUM ( 'Item Table'[Net Amount] ),
    ALL ( 'Calender Table'[Date] ),
    'ITEM Table'[Expiration Date] <- DATE ( 2019, 1, 31 )
    )
    If you want to use the values in the date segmentation, but in the Due date column instead, you can use this one.
    Add SlicerDates expiration date ?
    CALCULATE (
    SUM ( 'Item Table'[Net Amount] ),
    ALL ( 'Calender Table'[Date] ),
    TREATAS ( VALUES ( 'Calender Table'[Date] ), 'Item Table'[Expiration Date] )
    )
    In either, if you do not want to remove the filter from the Date column of the calendar[Date], remove the ALL() part.

    If this works for you, mark it as the solution. Praise is also appreciated. Please let me know if you don't.

    Best regards

    Pat

mahoneypat's avatar
mahoneypat
Microsoft Employee
6 years ago

I looked at your pbix.  FYI that a simpler can be used in your model to get the same result

 

Measure 3 = SUM('ITEM Table'[Net Amount])
 
Also, when the slice is set to just Jan 2019, the result for item D is -2.6M which seems correct as there are only two rows for item D in Jan 2019 (-3.6M and 1.0M).
 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

  • azeemnazim's avatar
    azeemnazim
    Helper II
    6 years ago

    @mahoneypat Thanks for the solution, can please assist with the measure to plot another graph summarizing all transactions due on or before 31/01/19 basis Due Date Column

    • mahoneypat's avatar
      mahoneypat
      Microsoft Employee
      6 years ago

      I'm not sure exactly what you're looking for, so two options below.

      If you really want a measure with a fixed date filter, you can use this.

      Fixed sum due date ?
      CALCULATE (
      SUM ( 'Item Table'[Net Amount] ),
      ALL ( 'Calender Table'[Date] ),
      'ITEM Table'[Expiration Date] <- DATE ( 2019, 1, 31 )
      )
      If you want to use the values in the date segmentation, but in the Due date column instead, you can use this one.
      Add SlicerDates expiration date ?
      CALCULATE (
      SUM ( 'Item Table'[Net Amount] ),
      ALL ( 'Calender Table'[Date] ),
      TREATAS ( VALUES ( 'Calender Table'[Date] ), 'Item Table'[Expiration Date] )
      )
      In either, if you do not want to remove the filter from the Date column of the calendar[Date], remove the ALL() part.

      If this works for you, mark it as the solution. Praise is also appreciated. Please let me know if you don't.

      Best regards

      Pat