Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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. ...
123abc's avatar
123abc
Community Champion
2 years ago

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.

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      123abc
      Community Champion
      2 years ago

      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.