Columns and Totals Do Not Match

[PizzaJim]

Hi Everyone,

I am have created a measure to calculate sums over different time periods using: 

Sales Last 4 Weeks =

    VAR __MaxDate = MAX( 'Invoice Data'[Date] )

    VAR __Last4 = CALCULATE( SUM( 'Invoice Data'[Amount] ), FILTER( 'Invoice Data', 'Invoice Data'[Date] <= __MaxDate && 'Invoice Data'[Date] > __MaxDate - 28 ))

RETURN

    __Last4

When putting this measure into the matrix visual, the total is correct. When I place a column from the source data in the row, the breakdowns are incorrect, but the column total remains correct:

768,109 is the correct value, but I am not sure why adding a row pulls in a ton of data that doesn't belong. Somehow 1,878,798 is being pulled. There are many questions about the total being incorrect, but I haven't been able to find the inverse and it seems odd. 

Thanks for any help!

[Ashish_Mathur · Accepted solution]

Hi,

Try this.

1. Create a Calendar Table with calculated column formulas for Year, Month name and Month number.  Sort the Month name by the Month number
2. Create a relationship (Many to One and Single) from the Date column of the invoice table to the Date column of the Calendar Table
3. To any visual, filter, slicer, drag any Date dimension only from the Calnedar Table
4. Write these measures

Sales Last 4 Weeks = CALCULATE( SUM( 'Invoice Data'[Amount] ), datesbetween( 'calendar'[date],min('calendar'[date])-28,min('calendar'[date])))

This measure will give you the wrong result in the Total row.  Therefore, drag this measure below to the vissual

Measure = SUMX(VALUES('Calendar'[Date]),[Sales last 4 weeks])

If these measures do not work, then share the download link of the PBI file.  Show the problem there and the expected result.

[v-kongfanf-msft · Accepted solution]

Thanks for the reply from @Ashish_Mathur , please allow me to provide another insight:

Hi @PizzaJim ,

Try to modify your formula like below:

Sales Last 4 Weeks = 
VAR __MaxDate = MAX('Invoice Data'[Date])
VAR __StartDate = __MaxDate - 28
RETURN
    CALCULATE(
        SUM('Invoice Data'[Amount]),
        FILTER(
            ALL('Invoice Data'),
            'Invoice Data'[Date] <= __MaxDate &&
            'Invoice Data'[Date] > __StartDate
        )
    )

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur · Accepted solution]

Hi,

Try this.

1. Create a Calendar Table with calculated column formulas for Year, Month name and Month number.  Sort the Month name by the Month number
2. Create a relationship (Many to One and Single) from the Date column of the invoice table to the Date column of the Calendar Table
3. To any visual, filter, slicer, drag any Date dimension only from the Calnedar Table
4. Write these measures

Sales Last 4 Weeks = CALCULATE( SUM( 'Invoice Data'[Amount] ), datesbetween( 'calendar'[date],min('calendar'[date])-28,min('calendar'[date])))

This measure will give you the wrong result in the Total row.  Therefore, drag this measure below to the vissual

Measure = SUMX(VALUES('Calendar'[Date]),[Sales last 4 weeks])

If these measures do not work, then share the download link of the PBI file.  Show the problem there and the expected result.