cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

New Member

## Columns and Totals Do Not Match

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!

2 ACCEPTED SOLUTIONS
Super User

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Community Support

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,

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

2 REPLIES 2
Community Support

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,

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

Super User

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.