Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
regards
Solved! Go to Solution.
Hi @jamuka ,
To achieve this goal you need to make the following steps:
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êsHi @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êsHello @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.
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
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êsHello @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
Hi @jamuka ,
To achieve this goal you need to make the following steps:
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êsCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |