Previous year on year sales using DAX query

[abhishek_300]

I am trying to write a dax query which will show Year on year sales by Year,Quarter,Month,Week,day in tabular model which will be publish to Power BI dashboard.The below code works good till the last year data comparsion. However the numbers are not correct for the current year since it is comparing the data with the sales for the entire last April month. Can you share your views on this query .

For example :

The last year sales for April were $100 dollar howerver till todays' date 14th April the sales were 50$

so the year on year sales for April month should be $50 however the below dax query is showing around $100 which is incorrect.

 

Year on year Sales:=SUMX ( 
    VALUES ( 'Time'[YearMonthNumber] ), 
    IF ( 
        CALCULATE ( 
            COUNTROWS ( 
                VALUES ( 'Time'[Date] ) 
            ) 
        ) 
            = CALCULATE ( 
                VALUES ( 'Time'[MonthDays] ) 
            ), 
        CALCULATE ( 
            [Sales], 
            ALL ( 'Time' ), 
            FILTER ( 
                ALL ( 'Time'[YearMonthNumber] ), 
                'Time'[YearMonthNumber] 
                    = EARLIER ( 'Time'[YearMonthNumber] ) - 12
            ) 
        ), 
        CALCULATE ( 
            [ Sales], 
            ALL ( 'Time' ), 
            CALCULATETABLE ( 
                VALUES ( 'Time'[Financial Day of Month] ) 
            ), 
            FILTER ( 
                ALL ( 'Time'[YearMonthNumber] ), 
                'Time'[YearMonthNumber] 
                    = EARLIER ( 'Time'[YearMonthNumber] ) - 12
            ) 
        ) 
    ) 
)

[CheenuSing]

Hi @abhishek_300

Try the following

I am assuming you have a MasterCalendar Table.

1. Create a measure called 

    FirstDateCurMonth = Calculate ((FirstDate(MasterCalendar[DateKey])),All(MasterCalendar))

2. Create a measure called

   LastDateCurMonth = Calculate ((LastDate(MasterCalendar[DateKey])))

3. Create a measure called

   LastDatePrevYear = EDATE([LastDateCurMonth],-12)

4. Create  a meassure called

 FirstDatePrevYear = Date(Year([LastDatePrevYear]),Month([LastDatePrevYear] ),1)

5. Create a meassure called

   LastYearSamePeriod = CALCULATE (
[Sales] ,
Datesbetween(MasterCalendar[Datekey], [FirstDatePrevYear],[LastDatePrevYear]) )

Now plot your month, mtd sales and LastYearSamePeriod, it should work.

Note that this solution works on date basis, and not on month basis.  So when you try to compare non leap-year Feb month with previous leap year month, your totals for the previous year will be upto 28 Feb only.

If this works for you, please accept it as a solution and also give KUDOS.

Cheers

CheenuSing

[LaurentCouartou]

From the top of my head, the easier way to do this would be to add a calculated (or not) column to your date table, flagging all dates on or before "today"'s date to TRUE.

Wrap your expression in a CALCULATE expression, with the column you just added as a context argument.

Something like this

CALCULATE( YourExpression
  , 'Time'[InThePast]
)

[abhishek_300]

I tried changing the logic to the below however I am still seeing the Sales are getting calculated for the entire month for April and Quarter 2 and Year 2017.

Year on year sales:=SUMX ( 
    VALUES ( 'Time'[YearMonthNumber] ),
IF (
    LASTNONBLANK ( 'Time'[Date], [Gross Sales] ) >= MIN ( 'Time'[Date] ),
    CALCULATE ( 
            [Sales], 
            ALL ( 'Time' ), 
            FILTER ( 
                ALL ( 'Time'[YearMonthNumber] ), 
                'Time'[YearMonthNumber] 
                    = EARLIER ( 'Time'[YearMonthNumber] ) - 12
            ) 
        )
)
)

[abhishek_300]

This query also does not provide the correct logic. The data looks good till last year.

However the sales for April and Q2 and the year 2017 should be

CALCULATE([gross sales],SAMEPERIODLASTYEAR('Time'[DATE]))

[v-shex-msft]

Hi @abhishek_300,

You can try to use below formula: (calculate based on year, week number, weekday)

Measure = 
var currDate=MAX('Table'[Date])
return
 SUMX(FILTER(ALL('Table'),YEAR('Table'[Date])=YEAR(currDate)-1&&WEEKNUM('Table'[Date],1)=WEEKNUM(currDate,1)&&WEEKDAY('Table'[Date],1)=WEEKDAY(currDate,1)),[Value])

SAMEPERIODLASTYEAR will return the same date of previous year.

Regards,

Xiaoxin Sheng

[shzyincu]

I need to calculate percentage change in months from two years, i have years 2016, 2017 and i want to compare similar months from both years, please if you can help?

[abhishek_300]

I am using tabular model 2014. This query won't work in 2014 I guess