Applying two different calendars on the same data

[kukszi]

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

[Jihwan_Kim · Accepted 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.

Internal Stakeholders =

CALCULATE (

SUM ( Sales[Sales] ),

TREATAS ( VALUES ( 'Calendar'[Reporting Week] ), Sales[Week] )

)

External Stakeholders =

CALCULATE (

SUM ( Sales[Sales] ),

TREATAS ( VALUES ( 'Calendar'[Billing Week] ), Sales[Week] )

)

https://www.dropbox.com/s/pgq85cxy951e6ki/kukszi.pbix?dl=0 

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

[Thejeswar · Accepted solution]

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

[Jihwan_Kim · Accepted 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.

Internal Stakeholders =

CALCULATE (

SUM ( Sales[Sales] ),

TREATAS ( VALUES ( 'Calendar'[Reporting Week] ), Sales[Week] )

)

External Stakeholders =

CALCULATE (

SUM ( Sales[Sales] ),

TREATAS ( VALUES ( 'Calendar'[Billing Week] ), Sales[Week] )

)

https://www.dropbox.com/s/pgq85cxy951e6ki/kukszi.pbix?dl=0 

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