Combining 2 Data Sources to Produce Year over Year Results

[Canknucklehead2]

Trying to create a simple line chart for Year over Year comparison of sales.

I have two excel files in different formats. One for FY2022 and one for FY2023. They are linked via Customer Number but the column layouts are too different to allow a proper Join or Append. 

I also have a Calendar Dimension. Both files are linked to the Calendar via Invoice Date fields.

I've created the measures to sum up the data by months and those appear to be correct. But a line graph will only populate FY2023 information. I've added a Date Slicer and I've tried setting to Year, FY, Month, Fiscal Period - the line graph will change for FY2023, but FY2022 information just does not populate. I've also tried different graphs, but the FY2022 information won't populate. I know there's an error somewhere in my measure, but I'm not certain what it is. 

Invoice Sales MTD = TOTALMTD([Sales Amount], 'DIM Calendar'[Date]) provides me the relevant information for FY2023 and the line chart populates up until this month.

For FY2022, I've tried:

Invoice Total LYMTD = CALCULATE('FY2022 Invoice Headers'[Sales LY Total], DATEADD('DIM Calendar'[Date],-1,YEAR))

and

Invoice Total LYMTD = totalmtd([Sales LY Total], 'DIM Calendar'[DATE])

Any help is appreciated.

[Canknucklehead2 · Accepted solution]

And, after sorting through everything, I finally noticed that the DATE column in the FY2022 table was a TEXT field and not a DATE field. **bleep** POWER BI and it's assumptions! Ugh!

Problem solved, now that it sees the date as an actual calendar date and not just text.

[v-tangjie-msft]

Hi @Canknucklehead2 ,

According to your description, here are my steps you can follow as a solution.

(1) We can create a measure. 

Combined Sales = 
VAR FY2022_Sales = SUM('FY2022 Invoice Headers'[Sales LY Total])
VAR FY2023_Sales = SUM('FY2023 Invoice Headers'[Sales LY Total])
RETURN
IF(ISBLANK(FY2022_Sales), 0, FY2022_Sales) + IF(ISBLANK(FY2023_Sales), 0, FY2023_Sales)

(2) Then the result is as follows.

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

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