Granularity into different Tables

DAX DAX Calculations
1

Hi Guys, and thank you for the support!

I have my Daily forecast revenue in the high granularity (Product Category) in one table and another with my Product details:

Forecast table:

Date; Category Forecast; Value
01/12/2021 ; Category 1 ; $10,000
01/12/2021; Category 2 ; $12,000
01/12/2021; Category 3 ; $12,500
02/12/2021 ; Category 1 ; $10,000
02/12/2021; Category 2 ; $12,000
02/12/2021; Category 3 ; $12,500
03/12/2021 ; Category 1 ; $9,000
03/12/2021; Category 2 ; $10,000
03/12/2021; Category 3 ; $11,500

Dim Products:
Product Name ; Category
Product 1, Category 1;
Product 2, Category 1;
Product 3, Category 1;
Product 4, Category 2;
Product 5, Category 2;
Product 6, Category 2;
Product 7, Category 3
Product 8, Category 3
Product 9, Category 3;

I want to join these two tables (Result):
Forecast table
Date ; Category; Product name; Forecast Reveneu:
01/12/2021 ; Category 1; Product 1 ; $10,000
01/12/2021 ; Category 1; Product 2 ; $10,000
01/12/2021 ; Category 1; Product 3 ; $10,000
01/12/2021; Category 2; Product 4 ; $12,000
01/12/2021; Category 2; Product 5 ; $12,000
01/12/2021; Category 2; Product 6 ; $12,000
01/12/2021; Category 3 ; Product 7; $12,500
01/12/2021; Category 3; Product 8 ; $12,500
01/12/2021; Category 3; Product 9 ; $12,500
02/12/2021 ; Category 1; Product 1 ; $10,000
02/12/2021 ; Category 1; Product 2 ; $10,000
02/12/2021 ; Category 1; Product 3 ; $10,000
02/12/2021; Category 2; Product 4 ; $12,000
02/12/2021; Category 2; Product 5 ; $12,000
02/12/2021; Category 2; Product 6 ; $12,000
02/12/2021; Category 3 ; Product 7; $12,500
02/12/2021; Category 3; Product 8 ; $12,500
02/12/2021; Category 3; Product 9 ; $12,500
03/12/2021 ; Category 1; Product 1 ; $9,000
03/12/2021 ; Category 1; Product 2 ; $9,000
03/12/2021 ; Category 1; Product 3 ; $9,000
03/12/2021; Category 2; Product 4 ; $10,000
03/12/2021; Category 2; Product 5 ; $10,000
03/12/2021; Category 2; Product 6 ; $10,000
03/12/2021; Category 3 ; Product 7; $11,500
03/12/2021; Category 3; Product 8 ; $11500
03/12/2021; Category 3; Product 9 ; $11,500

The Products table is a normal table, but the Forecast table is a calculated table and very dynamic (based on multiple factors ).

Thank you
Warm Regards
Jose Milhazes

2 Likes
2

Hey Jose,

can you provide a PBIX file please? It makes life a whole lot easier for answering your question.

Thanks

DJ

1 Like
3

Hi @DavieJoe , let me make one!

1 Like
4

Hi Guys, in attach you can find the .pbix sample! What I’m trying to accomplish is to have the future open days with forecast values!

image
image828×261 19.8 KB

Warm Regards
Jose Milhazes
example V2.pbix (237.3 KB)

5

@JoseMilhazes ,

I addressed an almost identical question over the weekend. Please take a look at the following thread, as well as the presentation I gave at last week’s Analytics Summit that provided the background for the thread below:

My strong recommendation would be to create your forecast table in Power Query, so that you can do the allocation there as well (since PQ can’t access DAX calculated tables). Because DAX calculated tables update only on refresh or initialization of the report, they are equally dynamic as PQ-created tables.

I hope this is helpful.

  • Brian
2 Likes
6

Thank you @BrianJ ,

Did solve in Power query was much easier!

Warm Regards
Jose Milhazes

2 Likes
7

@JoseMilhazes ,

Great – glad that got you what you needed. I agree that the PQ approach is much easier, and also means subsequently you don’t need to carry the TREATAS structure around in all of your related measures like you do when you solve this via DAX.

– Brian

1 Like