Year over Year Same Period Bar Chart

[AV155]

Hello,

Very new to Power BI.    I have looked at several similar posts on this topic and I think I am very close, but still having an issue.

I have 2 tabes.   

'Dates' with field Date.

'Sales' with fields ID, InvoiceDateTime, Profit, Mode  (multiple records per InvoiceDateTime)

I have created two Measures:

TotalProfit = CALCULATE(SUM(Sales[Profit]))

ProfitLastYearPeriod = CALCULATE([TotalProfit], SAMEPERIODLASTYEAR(Sales[InvoiceDateTime].[Date]))

When I create a table visualization with InvoiceDateTime, TotalProfit, and ProfitLastYearPeriod the data looks correct.

Now, I want to create a clustered bar chart with X-Axis being Sales.Mode which shows total profit Year to Date vs total profit Previous Year to date  (i.e. 1/1/2020 - 1/14/2020 vs 1/1/2021 - 1/14/2021).   When I add a filter to the chart like "Relative date, is in this year",  all of the values for ProfitLastYearPeriod drop off.

I'm either doing the filtering wrong or my measure is wrong.  I feel like I be referencing the Dates table in the SAMEPERIODLASTYEAR function, but when I do so the data is incorrect when I view in a table (shows the same value as current year instead).   Where am I going wrong here?

[AV155]

Thank you @amitchandak .  I had to resolve an issue with the relationship but I am now using my Date table in that measure as you suggested.

The issue I have now is the measure for last year is including all the dates for this month for some reason?

You see the total on the right does not match the sum of the values above it, but rather it is including the values of the complete month of January 2020.  This table has the filter of relative date - this year.  The true total of the right column should be ~116K.

[v-shex-msft · Accepted solution]

Hi @AV155,

I'd like to suggest you take a look at the following blog about use date functions do time intelligence and measure total level calculations if they suitable for your scenario:

Time Intelligence "The Hard Way" (TITHW)  

Measure Totals, The Final Word 

Regards,

Xiaoxin Sheng

[amitchandak]

@AV155 , First of all use date table in such case

ProfitLastYearPeriod = CALCULATE([TotalProfit], SAMEPERIODLASTYEAR(Date[Date]))

or you can try

Year behind Profit = CALCULATE([TotalProfit],,dateadd('Date'[Date],-1,Year))

Prefer not to use .date, create  a date column and join with Date of date table

Date = [InvoiceDateTime].date
or
Date = date(year([InvoiceDateTime]),month([InvoiceDateTime]),day([InvoiceDateTime]))

refer my video why TI fails https://www.youtube.com/watch?v=OBf0rjpp5Hw