- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Separate date tables?
Hello! I need some help regarding current and previous period dax expressions.
In our organization, we analyze variances from the most recent forecast to the previous forecast, in which all is contained in a sales table. Therefore, there are two types of time dimensions:
1) sales date, in which the sales have happened or expected to happen and is divided by fiscal periods
2) the forecasted date, which is the date the forecast is performed
I have a calendar table and this is linked to the sales date column in the fact table. As our forecast dates in the future could possible change from one day to another, I would prefer not to have a column in the fact table with the forecast date. I think it’s best to manage this separately by creating a separate table aside from a calendar table in which I can link to the sales table by an ID on both tables (e.g., “Jan _ forecast”). The following expression to calculate revenues returns blank. I have tried using “filter” as well as “related” functions but nothing seems to work and I can’t wrap my head around why.
VAR current_forecast = CALCULATE(MAX('Forecast_Date'[Forecast_Date_Table]),'Forecast
Return
Calculate([FY20_ForecastedRev],FILTER(RELATEDTABLE('Forecast_Date_Table'), 'Forecast_Date_Table'[Forecast_Date] = current_forecast))
Moreover, the cardinality on the tables seems to be fine. Does it make sense to have two separate tables? I have been looking for a solution to review a forecast over forecast scenario but I haven’t found anything on the forum that has helped so far so if anyone can point me in the right direction or has any suggestions or advice, I’d be greatly appreciative!!!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
Please share a simle sample datasets(s) and show the expected result.
Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @Ashish_Mathur ,
Thank you for your reply. Below is a sample date set:
Date Input | Fiscal Month | Fiscal Year | Fiscal Quarter | EOM Sales Date | Forecast Name | Forecast Date | Forecasted Amount |
Revenues | Jul | 2021 | Q1 | 31-Jul-20 | Dec_ forecast | 9-Dec-19 | € 1,575,000.00 |
Revenues | Aug | 2021 | Q1 | 31-Aug-20 | Dec_ forecast | 9-Dec-19 | € 1,507,500.00 |
Revenues | Sep | 2021 | Q1 | 30-Sep-20 | Dec_ forecast | 9-Dec-19 | € 1,590,000.00 |
Revenues | Jul | 2021 | Q1 | 31-Jul-20 | Jan _ forecast | 13-Jan-20 | € 1,522,500.00 |
Revenues | Aug | 2021 | Q1 | 31-Aug-20 | Jan _ forecast | 13-Jan-20 | € 1,510,500.00 |
Revenues | Sep | 2021 | Q1 | 30-Sep-20 | Jan _ forecast | 13-Jan-20 | € 1,590,000.00 |
Revenues | Jul | 2021 | Q1 | 31-Jul-20 | Feb _ forecast | 10-Feb-20 | € 1,545,000.00 |
Revenues | Aug | 2021 | Q1 | 31-Aug-20 | Feb _ forecast | 10-Feb-20 | € 1,515,000.00 |
Revenues | Sep | 2021 | Q1 | 30-Sep-20 | Feb _ forecast | 10-Feb-20 | € 1,590,000.00 |
This is the forecast date table (not the same as the calendar table, which is linked to the sales date):
Forecast Date | Forecast Name | Index |
9-Dec-19 | Dec _ forecast | 1 |
13-Jan-20 | Jan _ forecast | 2 |
10-Feb-20 | Feb _ forecast | 3 |
9-Mar-20 | Mar _ forecast | 4 |
13-Apr-20 | Apr _ forecast | 5 |
11-May-20 | May _ forecast | 6 |
8-Jun-20 | Jun _ forecast | 7 |
13-Jul-20 | Jul _ forecast | 8 |
10-Aug-20 | Aug _ forecast | 9 |
14-Sep-20 | Sep _ forecast | 10 |
12-Oct-20 | Oct _ forecast | 11 |
9-Nov-20 | Nov _ forecast | 12 |
And what I would like to do is explain the following:
Q1 results based on Dec_Forecast is 4.672M
Q1 results based on Jan_Forecast is 4.623M (there is a -49.5K variance from Jan's forecast and Dec's forecast)
Q1 results based on Feb_Forecast is 4.650M (there is a +27K variance from Feb's forecast and Jan's forecast)
I would like that the measures that calculate the sum of each months' forecasted revenues have a forecast date. And instead of having the forecast date in a column in the sales table, I would like to have it on a separate table, which I can update on the fly in the editor (if needed).
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
Could you also show me your expected result in another table? Thank you.
Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
HI @Cali_2020,
You can use following calculate table formula to extract and summarize records from the sample data table:
Forecaste = SUMMARIZE('Table',[Forecast Date],[Forecast Name],"Forecasted Amount",SUM('Table'[Forecasted Amount]))
Regards,
Xiaoxin Sheng
If this post helps, please consider accept as solution to help other members find it more quickly.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @v-shex-msft ,
Thanks for your reply but how can you use this formula with variables?
Thanks again!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
HI @Cali_2020,
So you mean 'Forecasted Amount' is a measure formula that summarizes other table records? If this is a case, you can use measure formula in iterator functions to summarize function expression fields.
BTW, current power bi does not support to create a dynamic calculated column/table based on filter or slicer. If you measure formula are dynamic based on filter, its result will be fixed in the calculated table formula.
Regards,
Xiaoxin Sheng
If this post helps, please consider accept as solution to help other members find it more quickly.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I prefer to have a single date dimension. Unless requirement forces it. The Advantage is that one can put data together. So Common dimensions allow seeing data together. Sales and Forecast fact should be separate, not all filter of sales will be used for forecasting.
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview. ✨
️ November 12th-14th, 2024
Online Event
Register Here
Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Power BI Monthly Update - January 2025
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
122 | |
80 | |
47 | |
44 | |
35 |
User | Count |
---|---|
182 | |
83 | |
70 | |
47 | |
45 |