compare data with different dates

[Kandidel]

Hi there, I need to create a report on the accuracy of the orderbook from last month compared to actual sales for this month and can't get my head around a solution. Your help is highly appreciated.

I have following tables:

Fact table "Orderbook"
Project ID	Reporting Date	Revenue Recognition Period	Sales
ABC	31.12.2023	202401	50
ABC	31.12.2023	202402	100
ABC	31.12.2023	202403	90
DEF	31.12.2023	202401	30
DEF	31.12.2023	202402	40
DEF	31.12.2023	202403	50

Fact table "Actual Sales"
Project ID	Reporting Date	Sales
ABC	31.1.2024	30
DEF	31.1.2024	50

Dim Table "Calendar"
Date	Period
31.12.2023	202312
1.1.2024	202401
...	...

Dim Table "Projects"
Project ID	Project Name
ABC	abc
DEF	def
...	...

with following relationships

Dim Table "Projects"		Table "Orderbook"
Project ID	1:*	Project ID
Dim Table "Projects"		Table "Actual Sales"
Project ID	1:*	Project ID
Dim Table "Calendar"		Table "Orderbook"
Date	1:*	Reporting Date
Dim Table "Calendar"		Table "Actual Sales"
Date	1:*	Reporting Date

My desired output is following:
Slicer (from calendar table) on period 202401
with a table showing these results:

Project ID	Act Sales Jan 2024	Sales from Orderbook Dec 2023 for Revenue Recognition Period Jan 2024
ABC	30	50
DEF	50	30

Any proposals? Thanks!

[Kandidel]

@123abc Thank you very much. There is one last issue now:

When I drop both of my measures (Actual Sales and expected Sales from Orderbook) in a matrix now, the orderbook measure is not split by Project ID, there is only a total.

The dim table for Projects has an established relationship to both fact tables (Actual Sales and Orderbook).
Do you have some wisdom to share?

Project ID	Act Sales Jan 2024	Sales from Orderbook Dec 2023 for Revenue Recognition Period Jan 2024
ABC	30
DEF	50
GEH	10
IJK	20
Total	110	120

[123abc]

To achieve the desired output, you can create relationships between the tables and then create calculated columns or measures to compute the necessary values. Here's how you can do it step by step:

1. Create Relationships: Ensure that the following relationships are established:

   • Dim Table "Projects" connected to Fact Table "Orderbook" and Fact Table "Actual Sales" via the "Project ID" column.
   • Dim Table "Calendar" connected to Fact Table "Orderbook" and Fact Table "Actual Sales" via the "Date" column.

2. Create Calculated Columns or Measures: You'll need to create calculated columns or measures to extract and compare data from the Fact tables based on the reporting date and sales period.

3. Write DAX Measures: Create DAX measures for actual sales and sales from the order book, filtering data based on the slicer selection.

Here's the sample DAX code to create the measures:

Actual Sales Jan 2024 =
CALCULATE(
SUM('Actual Sales'[Sales]),
FILTER(
'Calendar',
'Calendar'[Period] = 202401
)
)

Sales from Orderbook Dec 2023 for Revenue Recognition Period Jan 2024 =
CALCULATE(
SUM('Orderbook'[Sales]),
FILTER(
'Orderbook',
'Orderbook'[Revenue Recognition Period] = 202401 &&
'Calendar'[Period] = 202312
)
)

Ensure that you replace table and column names with the appropriate names used in your dataset.

1. Design the Report: Now, you can design your report with a slicer for the period from the Calendar table and a table visual that shows the Project ID, Actual Sales Jan 2024, and Sales from Orderbook Dec 2023 for Revenue Recognition Period Jan 2024.

When you select the period 202401 in the slicer, the table should show the actual sales for January 2024 and sales from the order book for the Revenue Recognition Period January 2024, as per your requirements.

This approach should help you generate the desired report accurately. Make sure to adjust the DAX measures and relationships based on the specific structure of your tables and data.

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

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[Kandidel]

Hi @123abc , thank you very much for you proposal! Relations are already implemented. 

I have tried the measure Sales from Orderbook Dec 2023 for Revenue Recognition Period Jan 2024, but needed to change the fixed text on periods

" 'Orderbook'[Revenue Recognition Period] = 202401 && 'Calendar'[Period] = 202312 "

to

"'Orderbook'[Revenue Recognition Period] = Calendar[Date] && 'Calendar'[Period] = ???",

because the user can select whatever period is wanted. And this change is not allowed. I cannot refer to that column. And even if this would be possible, what would be the appropriate entry for the "???" ?

Do you have another idea? Thanks a lot.
'Orderbook'[Revenue Recognition Period] = 202401 && 'Calendar'[Period] = 202312

[123abc]

One way to achieve this is by using DAX functions to calculate the sales from the orderbook based on the selected period. You can use the RELATED function to establish the relationship between the 'Orderbook' and 'Calendar' tables indirectly.

Here's a suggested measure:

Sales from Orderbook =
VAR SelectedPeriod = SELECTEDVALUE('Calendar'[Period])
VAR SelectedDate = CALCULATE(MAX('Calendar'[Date]), 'Calendar'[Period] = SelectedPeriod)
RETURN
CALCULATE(
SUM('Orderbook'[Sales]),
FILTER(
'Orderbook',
'Orderbook'[Reporting Date] = SelectedDate &&
'Orderbook'[Revenue Recognition Period] = SelectedPeriod
)
)

In this measure:

• SelectedPeriod retrieves the period selected in the slicer.
• SelectedDate calculates the corresponding date for the selected period.
• The FILTER function filters the 'Orderbook' table where the reporting date matches the selected date and the revenue recognition period matches the selected period.
• Finally, CALCULATE is used to sum up the sales for the filtered rows.

You can use this measure in your table visualization alongside the slicer on the period from the 'Calendar' table to dynamically display the sales from the orderbook based on the selected period.

Please adjust the measure and column names as per your actual data model if they differ. Let me know if you need further assistance!

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

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.