cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jamuka
Helper II
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 viewMy current table viewexpected table viewexpected table view

 

 

regards

 

 

 

2 ACCEPTED SOLUTIONS
MFelix
Super User III
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:

MFelix_0-1620312480976.png

  • 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:

MFelix_1-1620312625734.png

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

MFelix
Super User III
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:

MFelix_0-1620385840015.pngMFelix_1-1620385855872.png

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


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

5 REPLIES 5
jamuka
Helper II
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 tablecurrent 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

 

sample file 

 

MFelix
Super User III
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:

MFelix_0-1620385840015.pngMFelix_1-1620385855872.png

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


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

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

MFelix
Super User III
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:

MFelix_0-1620312480976.png

  • 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:

MFelix_1-1620312625734.png

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

thank you for your help @MFelix.

 

kind regards 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors