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 that these two have negative values of the net amount in them I have tried the below measures but they were of no help.

Measure = CALCULATE(SUM('ITEM Table'[Net Amount]),FILTER('ITEM Table','ITEM Table'[Item Code]=MAX('ITEM Table'[Item Code])))
and 
Measure 2 = IF(HASONEFILTER('Calender Table'[Date]),SUM('ITEM Table'[Net Amount]),SUMX(FILTER('ITEM Table','ITEM Table'[Item Code]=MAX('ITEM Table'[Item Code])),'ITEM Table'[Net Amount]))
 
according to me the value of item code D when the date set in the slicer is 31/01/2019 should be (-1,29,993) but it is coming something else. Below is the link to my pbix file.
 
 
and below is the link to the excel data source and question.
  • 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

9 Replies

  • Fowmy's avatar
    Fowmy
    Super User
    6 years ago

    azeemnazim 

    Not sure about what you are trying to calculate here. However, I hope you select dates up to 31/01/2020. but you have values for D after that particular date.

    Please explain more clearly.

    ________________________

    Did I answer your question? Mark this post as a solution, this will help others!.

    Click on the Thumbs-Up icon if you like this reply 🙂

    YouTube, LinkedIn

    • azeemnazim's avatar
      azeemnazim
      Helper II
      6 years ago

      Hi,

      The values in the visualization are not coming up correctly due to negative value i guess, Moreover, I have attached an excel file for data and my pbix file for a complete analysis

      Thanks.

       

      • azeemnazim's avatar
        azeemnazim
        Helper II
        6 years ago

        Fowmy 

         The issue due to negative value i guess , Moreover i have attached the pbix file and excel the values are not coming up correctly for item code A and D

  • 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