cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Microsoft t-atgeor
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.

 

test.jpg                                                                                     

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

 

Thank you. Atanas

11 REPLIES 11
BhaveshPatel Super Contributor
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.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.
Microsoft t-atgeor
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

BhaveshPatel Super Contributor
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.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.
Microsoft t-atgeor
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.

 

TOTALQTD(SUM('Table'[Cost in USD]), DATEADD('Table'[Fiscal Month],-1,QUARTER))

 

Any idea regarding it?

 

Thanks, Atanas

Microsoft AlexChen
Microsoft

Re: Month to Month / Quarter to Quarter differences

Hi,

 

I assume your table look like below.

 
3.png

 

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.

 

4.png

 

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:

 

5.png

 

Best Regards

Alex

 

Microsoft t-atgeor
Microsoft

Re: Month to Month / Quarter to Quarter differences

Hi @AlexChen

 

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

BhaveshPatel Super Contributor
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.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.
Microsoft AlexChen
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.

 

1.png

 

Second question,

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

2.png

 

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

 

3.png

 

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.

 

5.png

 

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 t-atgeor
Microsoft

Re: Month to Month / Quarter to Quarter differences

Hi @AlexChen, @BhaveshPatel

 

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

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

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

Microsoft Implementation for Communities Wins Award

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

Power Platform World Tour

Find out where you can attend!

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