Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Kandidel
Frequent Visitor

compare data with different dates

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 IDReporting DateRevenue Recognition PeriodSales
ABC31.12.202320240150
ABC31.12.2023202402100
ABC31.12.202320240390
DEF31.12.202320240130
DEF31.12.202320240240
DEF31.12.202320240350

 

Fact table "Actual Sales" 
Project IDReporting DateSales
ABC31.1.202430
DEF31.1.202450

 

Dim Table "Calendar"
DatePeriod
31.12.2023202312
1.1.2024202401
......

 

Dim Table "Projects"
Project IDProject Name
ABCabc
DEFdef
......


with following relationships

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



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

Project IDAct Sales Jan 2024Sales from Orderbook Dec 2023 for Revenue Recognition Period Jan 2024
ABC3050
DEF5030


Any proposals? Thanks!

4 REPLIES 4
Kandidel
Frequent Visitor

@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 IDAct Sales Jan 2024Sales from Orderbook Dec 2023 for Revenue Recognition Period Jan 2024
ABC30 
DEF50 
GEH10 
IJK20 
Total110120

 

123abc
Community Champion
Community Champion

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.

 

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
Community Champion
Community Champion

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.