Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi there,
I have trouble with applying two different calendars on the same data. The sales data should be consolidated differently when it is presented to internal vs. external stakeholders.
The sales are managed by a third party agent, who is sending the line items weekly, which is then manually invoiced. This approach creates a situation when the sales for the last week of the month are being captured as revenue for the next period, but when reviewing the performance with the external stakeholder, they are interested about the sales that took place in the calendar month.
The example data looks like this:
Week | Sales |
4 | 100 |
5 | 95 |
6 | 80 |
7 | 85 |
8 | 70 |
Reporting Calendar looks like this:
Reporting Period | Week |
January 2021 | 4 |
February 2021 | 5 |
February 2021 | 6 |
February 2021 | 7 |
February 2021 | 8 |
Billing Calendar:
Billing Period | Week |
February 2021 | 4 |
February 2021 | 5 |
February 2021 | 6 |
February 2021 | 7 |
March 2021 | 8 |
So in this case when the data is presented to the internal stakeholders, the February sales is 360 units (invoiced between week 4-7), but when it is presented to external stakeholders, the sales for the month is 330 units (shipped between week 5-8).
What is the best approach to manage this kind of situation in Power BI?
Thanks,
kukszi
Solved! Go to Solution.
Hi, @kukszi
I believe you have a date-related column in your tables. If you have it, then the below measures might need to be fixed to match your model. However, the below can give you some ideas about how to approach your problem.
Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
Linkedin: linkedin.com/in/jihwankim1975/
Twitter: twitter.com/Jihwan_JHKIM
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi @kukszi ,
You can also achieve it by using two different tables for the calendar.
Join those tables to the data table like shown below.
This can help you build the data as you need
Hi @kukszi ,
You can also achieve it by using two different tables for the calendar.
Join those tables to the data table like shown below.
This can help you build the data as you need
Hi, @kukszi
I believe you have a date-related column in your tables. If you have it, then the below measures might need to be fixed to match your model. However, the below can give you some ideas about how to approach your problem.
Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
Linkedin: linkedin.com/in/jihwankim1975/
Twitter: twitter.com/Jihwan_JHKIM
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
94 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |