Forecast Order Number - Ignoring the last 2 years (DAX)

[Zoreen]

Hello everyone, 

I am super glad to be part of this community. I am new to Power BI and I am excited about the journey so far. 

I have a report I am working on which currently tracks Orders Request and Orders Delivered. I am trying to write a DAX measure to predict future order quantinty to help my team be more proactive. I have historic data from 2017 - 2022. Due to the pandemic, the data from 2020 - 2021 might not be too useful for my analysis.

My question, is there a way I can write a DAX measure to have this years filtered out, or will it be useful to have this in for analysis? I would also appreciate if I can get the right formular to help me with my analysis 😊  Please see a sample table below.

Thank you!!! 

Order Date	Total Requested	Delivery date	Total Delivered
5/15/2022	45	5/19/2022	40
6/15/2022	10	6/21/2022	8
7/15/2022	50	7/20/2022	30
8/15/2022	-
9/15/2022	-
10/15/2022	-

[v-yangliu-msft · Accepted solution]

Hi  @Zoreen ,

I created some data:

If you want to filter to this year's data, you can use measure to mark it and place it in Fliter for processing

Here are the steps you can follow：

1. Create measure.

Flag =
IF(
    MAX('Table'[Order Date])>=DATE(YEAR(TODAY()),1,1),1,0)

2. Place [Flag]in Filters, set is=1, apply filter.

3. Result:

What is the rule of total number of orders requested that you want to predict? Can you express it in the form of pictures? We can help you better

If you need pbix, please click here.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[amitchandak]

@Zoreen , Create a date table with year, month qtr etc

Addcolumns(calendar(date(2020,01,01), date(2021,12,31) ), "Month no" , month([date])
, "Year", year([date])
, "Month Year", format([date],"mmm-yyyy")
, "Month year sort", year([date])*100 + month([date])
, "Month",FORMAT([Date],"mmmm")
, "Month sort", month([DAte])

)

Join this with all dates. Only one join will be active, others you can activate using userelationship in measure

https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in...

Use slicer from date table to filter

[Zoreen]

Hi @amitchandak 

Thank you for your response and sharing this resource. I have a DimDate set up connected to my FactTable. I will use this formula for my Delivery date.

I am still struggling with the right formular to get a forecast total number of orders requested for the future. Currently I am trying to write a DAX formular that calculates the Compound Annual Increase Rate then use this value to Calculate the Forecast, but its getting a little too complicated for me. 

Thank you so much!