cancel
Showing results for
Did you mean:
Microsoft

## Month to Month / Quarter to Quarter differences

Hi,

would like to establish the following view. Am not sure how I could make it more dynamic, being updated each month, without taking any information manually.

Could you please support on the DAX query formula so I could prepare a similar view?

Thank you. Atanas

11 REPLIES 11
Super Contributor

## Re: Month to Month / Quarter to Quarter differences

Hi Atanas,

This video tutorial by Alberto Ferrari will guide you create the calculations you requested. Yes, this calculations are possible in PBI using DAX and they are called time intelligence functions.

Please watch to get indepth knowllege of Time Intelligence.

Thanks & Regards,

Bhavesh

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Microsoft

## Re: Month to Month / Quarter to Quarter differences

Hi Bhavesh,

thank you for the advice. Went through the video, and captured most of what I need, unfortunately, could not fully understand how the Previous month feature works.

Previous Month = TOTALMTD(SUM('Table'[Cost in USD]), PREVIOUSMONTH('Table'[Fiscal Month]))

Such a formula will show me the current month. How do I pull data for a full period before. In this case, July?

Thanks,

Atanas

Super Contributor

## Re: Month to Month / Quarter to Quarter differences

Hi Atanas,

Try this measure for getting the previous month results:

Previous Month := CALCULATE(SUM('Table'[Cost in USD]), DATEADD('Table'[Fiscal Month],-1,MONTH))

Thanks & Regards,

Bhavesh

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Microsoft

## Re: Month to Month / Quarter to Quarter differences

Hi Bhavesh, thanks, works perfectly, however, used the TotalMTD to get the data only for the month I need. What would be the case with  Quarter information?

Tried using the one below, i believe its totally wrong.

Any idea regarding it?

Thanks, Atanas

Microsoft

## Re: Month to Month / Quarter to Quarter differences

Hi,

I assume your table look like below.

If you want to calculate the previous month and previous quarter’s sum cost,

You can add a month and quarter column from “fiscal month” column in query editor.

Now you can create 2 measures:

pervious month sum cost = CALCULATE(SUM(fiscalInfo[cost in usd]), Filter(all(fiscalInfo), sumx(FILTER(fiscalInfo, fiscalInfo[month] = EARLIER(fiscalInfo[month]) + 1), fiscalInfo[cost in usd])))

pervious quarter sum cost = CALCULATE(SUM(fiscalInfo[cost in usd]), Filter(all(fiscalInfo), sumx(FILTER(fiscalInfo, fiscalInfo[quarter] = EARLIER(fiscalInfo[quarter]) + 1), fiscalInfo[cost in usd])))

This is the result:

Best Regards

Alex

Microsoft

## Re: Month to Month / Quarter to Quarter differences

thank you for responding, immense help and works fine, so far. Three short questions:

1. How do you create the column Quarter?

2. If I would like to see information from previous years, for example August 2015, this would overstep with August 2016 in the column Month, and therefore, in the final numbers. How do I avoid it?

3. I have Report Level Filters, which seem to be not captured in the measures mentioned. Is there a way to do so?

Thanks, Atanas

Super Contributor

## Re: Month to Month / Quarter to Quarter differences

Good Work Alex.

But I would suggest to use Time Intelligence in such scenarios as your formulas are quite memory expensive and would not particularly suitable for large data sets. It is nesting the two iterator functions FILTER and SUMX.

Thanks & Regards,

Bhavesh

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Microsoft

## Re: Month to Month / Quarter to Quarter differences

Hi t-atgeor,

First question,

You can go to query editor -> add column to create year/month/quarter/week by your "fiscal month" if it's a date type.

Second question,

I realized the issue. I updated my dateset by adding data of last year.

Now add new columns Year/Month/Quarter with the steps above.

add another 4 calculated columns using code below.

currentMonth = [Year] & [Month]

currentQuarter = [Year] & [Quarter]

lastMonth = if(fiscalInfo[Month] = 1, (fiscalInfo[Year] - 1) & 12, fiscalInfo[Year] & (fiscalInfo[Month] - 1))

lastQuarter = if(fiscalInfo[Quarter] = 1, (fiscalInfo[Year] - 1) & 4, fiscalInfo[Year] & (fiscalInfo[Quarter] - 1))

add 2 measure to calculate previous month and previous quarter's sum amount.

pervious month sum cost = CALCULATE(SUM(fiscalInfo[cost in usd]), Filter(all(fiscalInfo), sumx(FILTER(fiscalInfo, fiscalInfo[lastMonth] = EARLIER(fiscalInfo[currentMonth])), fiscalInfo[cost in usd])))

pervious quarter sum cost = CALCULATE(SUM(fiscalInfo[cost in usd]), Filter(all(fiscalInfo), sumx(FILTER(fiscalInfo, fiscalInfo[lastQuarter] = EARLIER(fiscalInfo[currentQuarter])), fiscalInfo[cost in usd])))

This is the result.

Third question,

What filter have you put in Report level filter? I put "fiscal month" into it and found it captured.

Can you share a screenshot about it?

Best Regards

Alex

Microsoft

## Re: Month to Month / Quarter to Quarter differences

thanks a lot, helped me big time. Am only wondering whether I could amend the quarter columns using a business quarter. Sometimes, the company's fiscal year does not comprehend with the calendar year, the fiscal year in my case starts in October.

E.g Quarter 4 of calendar year 2016 = Quarter 1 of fiscal year 2017

How do I show the correct quarter in this case?

Secondly, lets assume I have a slicer for the month and pick up February, my column Previous month shows me an error and not the cost of January. How do I amend the formula to have it flexible, based on the slicer Fiscal Month?

Thanks and regards,

Atanas

Announcements

Kudos to you if you earned one of these! Check your inbox for a notification.

#### Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

#### Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (1,143)