# YTD Sales Amount Measure - Calculating wrong field • ### Question

• Greeting everyone,

I'm kindly asking for some guidance. I've created four measures to help me calculate YTD Sales.How to get "YTD Sales" use the measure I'm specifiying it.

YTD Sales (Balance Change) is summing up measure "Current Month Sales" instead of the one I'm specifiying which is Current Month Sales - Previous Month Sales (Balance Change).

Here are the measure created.

• Current Month Sales
• Previous Month Sales
• Current Month Sales - Previous Month Sales (Balance Change)
• YTD Sales (Balance Change)

This is the output ....which YTD Sales BC is wrong! but the output for YTD Sales BC should look like this ```YTD Sales (Balance Change) = CALCULATE (
[Current Month Sales - Previous Month Sales (Balance Change)],
FILTER (
ALL ( Dates ),
Dates[Date] <= MAX ( Dates[Date] )
&& Dates[year] = MAX ( Dates[year] )
)
)```
`Current Month Sales = CALCULATE(SUM(Additional_Info[C Sales Amount]))`

```Previous Month Sales =
VAR CurrentMonth = SELECTEDVALUE('Calendar'[MonthOfYear])
VAR CurrentYear = SELECTEDVALUE('Calendar'[Year])
VAR MaxMonthNum = CALCULATE(MAX('Calendar'[MonthOfYear]),All ('Calendar'))

RETURN IF(HASONEVALUE('Calendar'[MonthOfYear]),
SUMX(FILTER(ALL('Calendar'),
IF(CurrentMonth = 1, 'Calendar'[MonthOfYear] = MaxMonthNum && 'Calendar'[Year] = CurrentYear-1,
'Calendar'[MonthOfYear] = CurrentMonth - 1 && 'Calendar'[Year] = CurrentYear)),
[Current Month Sales]),
BLANK())```

```Current Month Sales - Previous Month Sales BC = IF(RIGHT(MAX('Calendar'[Period]),2) = "01",
CALCULATE([Current Month Sales]),
CALCULATE([Current Month Sales] - [Previous Month Sales]))```

Any help is appreciated. Thank you!

Jeannette

• Edited by Tuesday, April 24, 2018 7:53 PM Organizing content
Tuesday, April 24, 2018 4:03 PM

• Your 2 issues are related. The missing total for the previous month calculation happens because you have an IF HASONEVALUE() check in your measure and at the total level this returns false because there are 12 months that make up the total for a year.

This is also what is causing the issue for your YTD balance change, because for anything after the first month there are month that one month so the IF HASONEVALUE check is returning blank so you end up just doing a YTD on the current month values.

So you need to adjust your prior month calculation to something that will work with multiple months.

You could do something like the following (which leverages the fact that there is no 0 month to blank out the value at month 1)

[prior month sales for balance] =
SUMX (
VALUES('Calendar'[Period])
, CALCULATE(
[Current Month Sales]
, VAR vPrior = 'Calendar'[Period] -1
RETURN filter ( ALL('Calendar')
, 'Calendar'[Period] = vPrior)
))

• Marked as answer by Wednesday, April 25, 2018 2:27 PM
Wednesday, April 25, 2018 3:25 AM
• Hi Jeannette,

```[Current Month Sales] =

[Previous Month Sales] =
CALCULATE([Current Month Sales], FILTER(ALL('Calendar'),'Calendar'[Period]=MAX('Calendar'[Period])-1))

[Current Month Sales - Previous Month Sales BC] =
SUMX(VALUES('Calendar'[Period]),[Current Month Sales]-[Previous Month Sales])

[YTD Sales BC] =
SUMX(FILTER(ALL('Calendar'),'Calendar'[Period]<=MAX('Calendar'[Period]) &&
LEFT('Calendar'[Period],4)=LEFT(MAX('Calendar'[Period]),4 )),
[Current Month Sales - Previous Month Sales BC])```

As I test with below sample data, everything works as expected:

```Sumsales =
sum(tab[Sales])

previous =
CALCULATE([Sumsales], FILTER(ALL(tab),tab[Weeknumber]=MAX(tab[Weeknumber])-1))

Balance Change =
sumx(VALUES(tab[Weeknumber]),[Sumsales]-[previous])

YTDSALES =
sumx(filter(all(tab),tab[Weeknumber]<=max(tab[Weeknumber])),tab[Balance Change])```    Best Regards
Willson Yuan
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

Wednesday, April 25, 2018 5:42 AM

### All replies

• To add to the question on why YTD Sales is using the wrong measure, I noticed there is no total for measure "Previous Month Sales." Tuesday, April 24, 2018 7:28 PM
• Your 2 issues are related. The missing total for the previous month calculation happens because you have an IF HASONEVALUE() check in your measure and at the total level this returns false because there are 12 months that make up the total for a year.

This is also what is causing the issue for your YTD balance change, because for anything after the first month there are month that one month so the IF HASONEVALUE check is returning blank so you end up just doing a YTD on the current month values.

So you need to adjust your prior month calculation to something that will work with multiple months.

You could do something like the following (which leverages the fact that there is no 0 month to blank out the value at month 1)

[prior month sales for balance] =
SUMX (
VALUES('Calendar'[Period])
, CALCULATE(
[Current Month Sales]
, VAR vPrior = 'Calendar'[Period] -1
RETURN filter ( ALL('Calendar')
, 'Calendar'[Period] = vPrior)
))

• Marked as answer by Wednesday, April 25, 2018 2:27 PM
Wednesday, April 25, 2018 3:25 AM
• Hi Jeannette,

```[Current Month Sales] =

[Previous Month Sales] =
CALCULATE([Current Month Sales], FILTER(ALL('Calendar'),'Calendar'[Period]=MAX('Calendar'[Period])-1))

[Current Month Sales - Previous Month Sales BC] =
SUMX(VALUES('Calendar'[Period]),[Current Month Sales]-[Previous Month Sales])

[YTD Sales BC] =
SUMX(FILTER(ALL('Calendar'),'Calendar'[Period]<=MAX('Calendar'[Period]) &&
LEFT('Calendar'[Period],4)=LEFT(MAX('Calendar'[Period]),4 )),
[Current Month Sales - Previous Month Sales BC])```

As I test with below sample data, everything works as expected:

```Sumsales =
sum(tab[Sales])

previous =
CALCULATE([Sumsales], FILTER(ALL(tab),tab[Weeknumber]=MAX(tab[Weeknumber])-1))

Balance Change =
sumx(VALUES(tab[Weeknumber]),[Sumsales]-[previous])

YTDSALES =
sumx(filter(all(tab),tab[Weeknumber]<=max(tab[Weeknumber])),tab[Balance Change])```    Best Regards
Willson Yuan
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

Wednesday, April 25, 2018 5:42 AM
• Thank you Darren! This is exactly what I needed.
Wednesday, April 25, 2018 2:28 PM
• Willson,