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. ...
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.