Prev Year monthly calculations to set goal for current year month

[sgk123]

Hi all,

I have a problem in calculating the below scenario by using measures.

Could you please help me with it.

I have a table with Product, Location, Year, Quarter, Month, Week (Year Week), Sales

Product	Location	Year	Quarter	Month	Week	Sales	Goal
P1	L1	2016	1	1	1	12	-
P1	L2	2016	1	1	2	2	-
P2	L1	2016	1	1	3	5	-
P3	L1	2016	1	1	4	1	-
P1	L3	2016	1	1	5	5	-
P4	L2	2016	1	1	6	5	-
P4	L4	2016	1	2	6	8	-
P2	L2	2016	1	2	7	15	-
P1	L1	2017	1	1	1	8	5.71428
P1	L2	2017	1	1	2	4	7.14285
P2	L1	2017	1	1	3	6	7.14285
P3	L1	2017	1	1	4	9	7.14285
P1	L3	2017	1	1	5	7	2.85714
P4	L2	2017	1	2	5	5	4.28571
P4	L4	2017	1	2	6	2	5.225
P2	L2	2017	1	2	7	8	5.225

Goal = ((Sum of sales of previous year same month)/(No of Working days in present year month))* (No of working days in present year that week)

How do I calculate goal using measures in Power BI?

[Datatouille]

Hi @sgk123

First of all, you need to have a proper 'WorkingDay' Column in your Calendar Table. This column must have 2 values: 1 for a working day and 0 for a non-working day.

Here is my suggestion (based on 4 DAX Measures):

1. Total Sales = Sum (YourTable[SalesAmount])

2. Total Sales LY SameMonth = VAR CurrY = MAX('Calendar'[Year]) VAR CurrMonth = MAX('Calendar'[Month]) Return
CALCULATE( [Total Sales] , FILTER(ALL('Calendar');'Calendar'[Year] = CurrY-1) , FILTER(ALL('Calendar') ,'Calendar'[Month] = CurrMonth ) )

3. Total Working Days = Sum(Calendar[WorkingDay])

4. WorkingDaysThisYthisMonth = Calculate ( Sum(Calendar[WorkingDay]) , All(Calendar[Week]) )

5. Goal = Total Sales LY SameMonth / WorkingDaysThisYthisMonth * Total Working Days

As you can see, I broke the measures into several pieces. It makes it clearer to follow the logic of the different steps.

Hope it helps you and solves your issue!

[sgk123]

Hi @Datatouille,

I have a working day in my calendar table.

In the step 2 of your solution

Total Sales LY SameMonth = VAR CurrY = MAX('Calendar'[Year]) VAR CurrMonth = MAX('Calendar'[Month]) Return
CALCULATE( [Total Sales] , FILTER(ALL('Calendar');'Calendar'[Year] = CurrY-1) , FILTER(ALL('Calendar') ,'Calendar'[Month] = CurrMonth ) )

Here this is calculating only for Current Month right? For example, it is caluclating only for March.

I need it for all the months

Please correct me if I was assuming wrong.

[Datatouille]

@sgk123: You are right, [Total Sales LY SameMonth] is computing the sales of Last year only for the month in the current filter context.

Isn't what you need? You said: "Sum of sales of previous year same month" in your requirements.

[sgk123]

Hi @Datatouille

Yeah, but my final requirement is to have that goal as a column added to the table.

I tried doing it, but was unable to.

Could you please help me with it

[v-qiuyu-msft]

Hi @sgk123,

In your scenairo, you can create below measures:

WDThisYM = CALCULATE(SUM('Calendar'[WorkingDay]),FILTER(ALL('Calendar'),Max('Table1'[Year])='Calendar'[Year] && Max('Table1'[Month])='Calendar'[Month]))

LYSales = CALCULATE(SUM('Table1'[Sales]),FILTER(ALL('Table1'),'Table1'[Year]=MAX('Table1'[Year])-1 && 'Table1'[Month]=MAX('Table1'[Month])))

WDThisYW = CALCULATE(SUM('Calendar'[WorkingDay]),FILTER(ALL('Calendar'),Max('Table1'[Year])='Calendar'[Year] && Max('Table1'[Month])='Calendar'[Month] && MAX('Table1'[Week])='Calendar'[Week]) )

Goal = 'Table1'[LYSales]/'Table1'[WDThisYM]*'Table1'[WDThisYW]

Best Regards,
Qiuyun Yu

[sgk123]

Hi @v-qiuyu-msft

This seems to be fine, but I'm not able to roll up on Product or location level.

The Goals work fine only if week is displayed in any of the graphs/tables

i.e., Total of Goals should be equal to 44.96 instead of 5.75