Dax SUM and GROUPBY

[Anonymous]

I have build a model in Power BI where several tables are linked

Model
This is the report I have build using a matrix that display total amount by zone and country over Year and Year -1.
I have created three measures to calculate CA Current Year and CA Last Year for each country and zone

1.  Total CA = SUM(Bookings[Price])
2.  CA Current Year = CALCULATE([Total CA],DATESYTD(DimDate[Date]))
3.  CA Last Year = CALCULATE([Total CA],SAMEPERIODLASTYEAR(DimDate[Date]))

Report
My problem is i'm not getting the correct amounts for the countries.
Below Query gives me the correct amount for the countries

select 
	Dest.Name as Zone,
	a.Name as country,
	YEAR(bk.DateBooking) as YearOfBooking,
	DATENAME(MONTH,DATEADD(month,MONTH(bk.DateBooking),-1)) as MonthName,
	round(sum(bk.price),0,0) as KEuros
from dbo.Bookings bk
inner join dbo.Customers cust on bk.IdCustomer = cust.Id
inner join ref.Countries C on c.Id =cust.IdCountry
inner join ref.CountriesTranslations ct on c.Id=ct.IdReferenceObject
inner join dbo.Bases B on bk.IdBaseEnd = B.Id
inner join dbo.Areas A on A.Id = B.IdArea
inner join dbo.Destinations Dest on Dest.Id = A.IdDestination
where year(DateBooking) > '2016'
and MONTH(DateBooking) in (6)
group by Dest.Name,YEAR(bk.DateBooking),MONTH(bk.DateBooking),A.Name
order by Zone,YearOfBooking

 Result of Query

Query Result
Is there a way so that I can get the correct values for each zone and countries?

[Anonymous · Accepted solution]

@Anonymous  - I see a couple of issues:

1.  CA Current Year = CALCULATE([Total CA],DATESYTD(DimDate[Date])) - This is a YTD calculation, but it seems you only want to see June. The measure [Total CA] should work for that purpose.

2. You have 2 years selected in the slicer. You only want one, because when you have both 2017 and 2018 selected, you're getting the SUM of 2016 and 2017.

Cheers!

Nathan

[Anonymous]

Thanks @Anonymous
You are right the measure [Total CA] = CA Current Year and you are also right for the second point as well.
Thanks a lots.
It helps me to resolve my problems.