From Week to Daily Forecasted Sales

DAX DAX Calculations
1

Hello,

Few days ago @Melissa helped me with a “WorkdayNumber field” within a 445 Calendar structure.

The problem that I am experiencing is that I would like to create a Daily Forecast by combining the data from “Daily Sales” and “Weekly Forecast”

*A mockup of the results I want to achieve would be extracting the weekly sales that took place per customer within the same week from the past years (2019 & 2020) and applying a daily weight according to the volumes sold within the days from the week in question. Then, when looking into the weekly forecast per customer it might be possible to detail a theoretical daily forecasted sales amount.

Thanks very much!

2

Bumping this post for more visibility.

1 Like
3

Hi @Jose,

I think your question is missing some crucial details. For example:

  • How do you define “weekly sales that took place per customer within the same week from the past years (2019 & 2020)” ?
  • Do you want the avg. of those years or just ‘the’ previous year of a given date?
  • “apply a daily weight according to the volumes sold within the days from the week” ?
  • How do you want to deal with weeks at the start- or end of the year AND/OR how does your custom calendar logic factor in?
  • …

Providing a mock up of the desired results in Excel (incl. formulas), will probably answer all of these and give members who are providing support a better understanding of your actual requirement.

Being very clear and specific about what you are trying to achieve and how you want to visualize results will not only get you a better answer but also a more swift response. Review all topics in the Forum Guides category for other useful tips on asking questions.

4

Hello @Jose, good to see that you are having progress with your inquiry.

Did the response from @Melissa help you solve your inquiry? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query.

5

Hi @Jose, we’ve noticed that no response has been received from you since August 30.

We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

6

Hello, thanks a lot for your help!
Hope to make myself a bit more clear:

  • How do you define “weekly sales that took place per customer within the same week from the past years (2019 & 2020)” ? (it is already defined within the Daily Sales Table, just need to filter by the specific customer within a specific date)
  • Do you want the avg. of those years or just ‘the’ previous year of a given date? (I want to convert the Weekly Forecast Table into a Daily Forecast, because the Daily Forecast Table registers what has been sold each day for a specific customer whereas the Forecast Tables predicts what would be sold for a specific customer in the future weeks to come)
  • “apply a daily weight according to the volumes sold within the days from the week” ? (my mistake for not being clear enough, not sure what would be the best way to break down the Weekly Forecast Table into a Daily Forecast Table)
  • How do you want to deal with weeks at the start- or end of the year AND/OR how does your custom calendar logic factor in? (This point seems to be covered from the logic applied when creating the 445WeekdayTable, still working with a 445 callendar )
  • …
7

Hi @Jose,

I changed the locale of the file to English, Europe as my spanish leaves something to be desired…
Other changes:

  1. Fixed type error in the Date table
  2. Marked the Dates table as Date table
  3. Added a Customer Dim table
  4. Added a Fiscal Year & Week column to the Forecast table: [Año natural] & “-” & [Semana APO (445)]
  5. Hid all key fields in fact tables
  6. Added aggregate measures for numerical fields

Created some measures:

CALCULATE(    
    SUM( 'Forecast'[Sales Amount] ),
    TREATAS( VALUES( Forecast[Fiscal Year & Week] ), Dates[Fiscal Year & Week] )
)

.

Daily weight = 
DIVIDE(
    [Total Sales],
    CALCULATE( [Total Sales], REMOVEFILTERS( Dates[Date], Dates[DayOfWeek], Dates[DayOfWeekName] ))
)

.

Daily FC by Fiscal week & year = 
VAR vTable = 
    ADDCOLUMNS(
        VALUES( Dates[Fiscal Year & Week] ),
        "Value", IF( [Total Sales] <> BLANK(), [Total Forecast] * [Daily weight] )
    )
VAR _Result = SUMX( vTable, [Value] )
RETURN
    _Result

.
See how you get on with this. Here’s your sample file.
Forum Topic_2.pbix (213.1 KB)

I hope this is helpful

8

Thanks very much @Melissa! It seems to work, however I am having some trouble when replicating what you have done to the confidential file I am working on.

It seems to be with how I have created “_Measures”, the following Error appears when I want to check the results.

image
image1873×793 139 KB

Lastly, I have not been able to replicate the “1. Fixed type error in the Date table” to the confidential file.

Thanks :slight_smile:

9

Hi @Jose,

It seems my spanish is improving :wink: I can make out that you are aggregating a string, did you incoporate (6) and add aggregate measures for numerical fields?

As for the second question. If you are using the exact same function query called: fx445Weekday
then replace the variable ChType (on line 107) with:

ChType = Table.TransformColumnTypes(ExpandNew,{{"Year", Int64.Type}, {"Date", type date}, {"YearOffset", Int64.Type}, {"YearCompleted", type logical}, {"QuarterOfYear", Int64.Type}, {"Quarter & Year", type text}, {"QuarternYear", Int64.Type}, {"QuarterOffset", Int64.Type}, {"QuarterCompleted", type logical}, {"MonthOfYear", Int64.Type}, {"DayOfMonth", Int64.Type}, {"Month Name", type text}, {"MonthShortName", type text}, {"Month Initial", type text}, {"Month & Year", type date}, {"MonthnYear", Int64.Type}, {"MonthOffset", Int64.Type}, {"MonthCompleted", type logical}, {"MonthEnding", type date}, {"DateInt", Int64.Type}, {"DayOfWeek", Int64.Type}, {"DayOfWeekName", type text}, {"Weekday Initial", type text}, {"WeekStarting", type date}, {"WeekOffset", Int64.Type}, {"WeekCompleted", type logical}, {"WeekEnding", type date}, {"Fiscal Year", type text}, {"Fiscal Quarter", type text}, {"FQuarternYear", Int64.Type}, {"Fiscal Period", Int64.Type}, {"FPeriodnYear", Int64.Type}, {"Fiscal Week", Int64.Type}, {"Fiscal Year & Week", type text}, {"FWeeknYear", Int64.Type}, {"IsAfterToday", type logical}, {"IsWorkingDay", type logical}, {"IsBusinessDay", type logical}, {"Day Type", type text}, {"FiscalYearOffset", Int64.Type}, {"IsCurrentFQ", type logical}, {"IsCurrentFP", type logical}, {"IsCurrentFW", type logical}, {"IsPYTD", type logical}, {"IsPFYTD", type logical}, {"WorkDayNum", Int64.Type}})

I hope this is helpful.

10

Thanks @Melissa,

but don´t know how to incorporate step 6 correctly. What I did was to add a new table and copy the measures “Daily weight” & “Daily FC by Fiscal week & year” within the table. From the shared file I saw the _Measures Table and tried to replicate what I saw visually as a table containing two measures and hidding the Column1…

11

Hi @Jose,

No problem!

It’s a best practice to create explicit measures for aggregating numerical fields. So when you have a table called “AM” that contains a column with sales values, called [Total Sales].

You first create a simple measure: AM Total Sales = SUM( ‘AM’[Total Sales] )
and hide the source column so it can’t be dragged onto the report canvas by mistake.

Using measure branching you can reuse that measure over and over again for more complex calculations. I hope that clarifies it, if it’s still unclear please review the Ultimate Beginners Guides in the Portal.

12

Hi @Melissa,

Thanks very much! Everything is working perfectly, the only thing which is not working is that I was not sufficiently specific about the Data Structure used for the report you created.

From what I have been able to understand from your measures, playing with the filter context while performing calculations leads to the desired outcome. Therefore, the time frame for “Forecast” is 2021 whereas the time frame por “Sales” is 2019, 2020 & 2021. Maybe this fact could be affecting the “Total Forecast” measure calculation, as when extrapolating the “Shared File” to the “Confidential File” it seems that “Total Forecast” values are too high.
Lastly, when trying to think about the “Daily FC by Fiscal week & year” measure there is a rule when working through past and future, (understanding past as everything that has been sold: “Total Sales” and understanding future as everything that is foreseen to be sold: “Total Forecast”) which is that: taking the current week onwards everything represents the “Total Forecast”, BUT taking the current week backwards (to the past) what was meant to be “Total Forecast” should be substituted by what has actually been sold “Total Sales” (maybe it could be the reasoning behind the “<> Blank ()” condition within the “VAR vTable” within “Daily FC by Fiscal week & year” ).
In relation with the lastmentioned I would be eagered to be offered some guidance on how to translate this rule.

Answering to your last reply @Melissa, the problem with the measure was that the values within the Data Source where labeled as Text so I could not perform calculations. Nevertheless, remembering and working on the basics in any discipline is fundamental. It would be nice if you could recomend me some course within the Portal as you have been offering guidance on how to do certain things.

Thanks for your help!

13

Hi @Jose,

Implement an IF-statment, like you would in Excel.
So if the condition in the first argument is met, it will evaluate to TRUE and return the second argument otherwise if it evaluates to FALSE it returns the optional third argument or a blank.

I hope this is helplful.

14

Hi @Jose, did the response provided by Melissa help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

15

Hi @Jose, we’ve noticed that no response has been received from you since Sep 8. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

16

Hi @Jose, due to inactivity, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please create a new thread.