MOM and QOQ Filters Calculation

[ashmozzart]

Hi,

I have a graph which compares Customer Name on the Y_Axis and SalesAmount on the X_Axis.They need a MOM and QOQ filters.What exactly QOQ/MOM i am referring is when i select a month in the filter it should compare SalesAmount that month and just its previous month for every customer.For Example if i select 2016 Jan,It should compare 2016 Jan and 2015 Dec salesAmounts for every customer. Samething for Quater also.Is it possible in powerBI?What are the DAX calculations should i use?Thanks in Advance.

[MattAllington]

You can use the dateadd function like this

Total Sales = sum(table[sales column])

total sales prior month = calculate([total sales], dateadd(calendar[date],-1,month))

you need a calendar table http://exceleratorbi.com.au/power-pivot-calendar-tables/

the quarter calc depends what you need (e.g. Is it a rolling quarter or fixed quarter?)

total sales prior quarter = calculate([total sales], dateadd(calendar[date],-1,quarter))

[ashmozzart]

Hi,

Thanks a lot for the reply.

When i created a derived column using below Dax,

PreviousOrders_ = CALCULATE(SUM(Sheet1[OrderAmount]),DATEADD(Sheet1[BillStartDate],-1,MONTH))

I am getting an empty with no data.May i know why?

[v-ljerr-msft]

Hi @ashmozzart,

As most Time Intelligence Functions (DAX) require an individual and consecutive Date table to work correctly, you may need create it using CALENDAR or CALENDARAUTO Function (DAX) if you don't have yet. And create a relationship between the Date table and your fact table with the Date column.

Assume your Date table is called "DateTable", then the formula to create the measure should like below.

PreviousOrders_ =
CALCULATE (
    SUM ( Sheet1[OrderAmount] ),
    DATEADD ( DateTable[Date], -1, MONTH )
)

Regards

[MattAllington]

This should be written as a measure, not a column.