Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jamuka
Advocate II
Advocate 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
Super User

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

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
Advocate II
Advocate 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 

 

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



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
Super User

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



thank you for your help @MFelix.

 

kind regards 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.