cancel
Showing results for
Did you mean:
Helper II

## Different Time Granularity in same matrix

I want to show my daily sales, daily stock and monthly sales forecast (which calculated every week) in same table.

My tables are connected via Product and Date tables. But I can not add my forecast figures to my table in a correct way. Attached you can find a sample file, I guesss I have to create a second date table or second relation between my date and forecast table yet I can not grasp the concept.

Below you can find my current table and expected table.

My current table viewexpected table view

regards

2 ACCEPTED SOLUTIONS
Super User III

Hi @jamuka ,

To achieve this goal you need to make the following steps:

• Create a new calendar table (in my case I called it ForecastingDate and used the Date table has the base)
• Make a relationship between this forecasting table and the Forecast Date in the Forecast table:

• Change the columns of the matrix from the Date[Month Name] column to the ForecastingDate[Month Name] column
• Redo your measures to the following:
``````Total Forecast = SUMX('Forecast Table','Forecast Table'[Forecast])

Total Sales =
SUMX (
FILTER (
'Sales Table',
'Sales Table'[Sales Date] IN VALUES ( ForecastingDate[Date] )
),
'Sales Table'[Sales Quantity]
)

Opening Stock =
VAR MaxStockDate =
CALCULATE (
MAX ( 'Stock Table'[Stock Date] ),
ALLEXCEPT ( 'Stock Table', 'Date' ),
FILTER (
ALL ( 'Stock Table'[Stock Date] ),
'Stock Table'[Stock Date] IN VALUES ( ForecastingDate[Date] )
)
)
VAR Result =
CALCULATE ( SUM ( 'Stock Table'[Stock] ), 'Date'[Date] = MaxStockDate )
RETURN
Result``````

Final result below and in attach PBIX file:

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Super User III

Hi @jamuka ,

What is the purpose of this visualization do you want to see the order vs delivery in the weeks? that should be done using the same date table and not a forecast table in the midle:

``````Total Delivery =
CALCULATE(SUM('Order Table'[Order Quantity]), USERELATIONSHIP('Date'[Date], 'Order Table'[Delivery date]))

Total Order = Sum('Order Table'[Order Quantity])``````

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

5 REPLIES 5
Helper II

Hello @MFelix ,

I tried to add Order Information to my matrix, Order Quantity and Delivery Quantity.

I was able to show Delivery Quantity in correct week, but my order quantity shown in delivery date's week instead of creation date's week.

current table

total order should be in Week1 instead of 3. Also I tried to find blog/articles about my topic and "IN" and "VALUES" but couldn't  find any good source, I'll be glad if you know where I can I find more information.

regards

Super User III

Hi @jamuka ,

What is the purpose of this visualization do you want to see the order vs delivery in the weeks? that should be done using the same date table and not a forecast table in the midle:

``````Total Delivery =
CALCULATE(SUM('Order Table'[Order Quantity]), USERELATIONSHIP('Date'[Date], 'Order Table'[Delivery date]))

Total Order = Sum('Order Table'[Order Quantity])``````

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Helper II

Hello @MFelix ,

sorry for late reply. We'd like to track to order week and delivery week in same row. But I realized that It wasn't necessary.

thank you for your help again.

regards

Super User III

Hi @jamuka ,

To achieve this goal you need to make the following steps:

• Create a new calendar table (in my case I called it ForecastingDate and used the Date table has the base)
• Make a relationship between this forecasting table and the Forecast Date in the Forecast table:

• Change the columns of the matrix from the Date[Month Name] column to the ForecastingDate[Month Name] column
• Redo your measures to the following:
``````Total Forecast = SUMX('Forecast Table','Forecast Table'[Forecast])

Total Sales =
SUMX (
FILTER (
'Sales Table',
'Sales Table'[Sales Date] IN VALUES ( ForecastingDate[Date] )
),
'Sales Table'[Sales Quantity]
)

Opening Stock =
VAR MaxStockDate =
CALCULATE (
MAX ( 'Stock Table'[Stock Date] ),
ALLEXCEPT ( 'Stock Table', 'Date' ),
FILTER (
ALL ( 'Stock Table'[Stock Date] ),
'Stock Table'[Stock Date] IN VALUES ( ForecastingDate[Date] )
)
)
VAR Result =
CALCULATE ( SUM ( 'Stock Table'[Stock] ), 'Date'[Date] = MaxStockDate )
RETURN
Result``````

Final result below and in attach PBIX file:

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Helper II

thank you for your help @MFelix.

kind regards

Announcements