topic Sales by month and calculating variance in DAX Commands and Tips

Sales by month and calculating variance

Qamrk — Fri, 16 Sep 2022 18:07:30 GMT

Hello,

I am using SQL and Excel data to calculate sales by month with multiple filters by using 2 different tables.

Sales = CALCULATE(SUM(tblOrderLine[orl_quantity]),

filter(all(tblOrderLine),

tblOrderLine[orl_productId]='Combined forecast'[Prod_Id] &&

RELATED(tblOrder[Year-Month])='Combined forecast'[Year-Month] &&

RELATED(tblOrder[ord_orderStatusName])="invoiced" && RELATED(tblOrder[ord_channelId])<>25

)

This shows no error but doesnt not give me the rite output.

After calculating the sales, I am calculating the variance between sales and forecast using the following

Variance = IF('Combined forecast'[Date]<TODAY(),IFERROR( ('Combined forecast'[Forecast]-'Combined forecast'[Sales])/('Combined forecast'[Forecast]+'Combined forecast'[Sales]),0),0)

And then I need to add the variance of previous 6 months for which I am using the following DAX(does not give the rite output)

NFM =

VAR EndDate=

TODAY()-1

Var StartDate=

EDATE(EndDate,-6)

Var Result=

CALCULATE(

SUM('Combined forecast'[Variance]),

DATESBETWEEN('Combined forecast'[Date],StartDate,EndDate)

)

Return

Result

And then I need to check if an item has Bias or not for which I am using the following that leads to error

Bias =

Switch(

True(),

'Combined forecast'[NFM]<-1,"Under",

'Combined forecast'[NFM]>1,"Over",

"Ok")

Can someone please help me with this? Thank you!

-Qamr

Re: Sales by month and calculating variance

lbendlin — Sat, 17 Sep 2022 17:49:44 GMT

Please provide sanitized sample data that fully covers your issue.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: Sales by month and calculating variance

Qamrk — Tue, 20 Sep 2022 01:28:36 GMT

Hello,

Thank you for the response. I tried to provide sample data. But please let me know if that doesn't make sense or if you have concerns.

Prod_Id	Date	Forecast	Sales	Variance	MFM	Bias
2001	1/12/2020	2500	comes from another table that has Prod_id. This will have multiple filters ; eg: channel id!=25; order_status_name="invoiced"	(Forecast-sales)/(Forecast+sales)	is um of last 12 calendar months variance	if NFM>2; Over and NFM<-2; under
2001	2/12/2020	2200
2001	3/12/2020	900
2001	4/12/2020	905
2001	5/12/2020	2300
2001	6/12/2020	2500
2001	7/12/2020	925
2003	1/12/2020	1200
2003	2/12/2020	1250
2003	3/12/2020	1100
2003	4/12/2020	1110
2003	5/12/2020	900
2003	6/12/2020	980
2003	7/12/2020	900
2003	8/12/2020	950
2003	9/12/2020	1000