cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Previous year on year sales using DAX query

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
            ) 
        ) 
    ) 
)

7 REPLIES 7
Highlighted
Solution Supplier
Solution Supplier

Re: Previous year on year sales using DAX query

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]
)
Highlighted
Frequent Visitor

Re: Previous year on year sales using DAX query

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
            ) 
        )
)
)

Highlighted
Frequent Visitor

Re: Previous year on year sales using DAX query

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 issue previous.JPG

 

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

Highlighted
Community Support
Community Support

Re: Previous year on year sales using DAX query

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Highlighted
Frequent Visitor

Re: Previous year on year sales using DAX query

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

Highlighted
Super User I
Super User I

Re: Previous year on year sales using DAX query

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

 

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
Highlighted
Helper I
Helper I

Re: Previous year on year sales using DAX query

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?

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Kudoed Authors