Month to Month / Quarter to Quarter differences

[t-atgeor]

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

[AlexChen]

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

[BhaveshPatel]

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

[t-atgeor]

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

[AlexChen]

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

[t-atgeor]

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

[BhaveshPatel]

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

[Pallavi]

I haven't found video in the enclosed link

[Pallavi]

Hi Bhavesh,

I am very new to DAX and PowerBI and need help to get values on card upto current quarter

I have a table with Column DPQuarter which have Q1, Q2, Q3 and Q4 respectively and my Q1 starts from April month. I have to show balances in each Quarter until this Quarter as follows

Q1(Apr, may, jun)

Vol balance       2250

P1 balance         50

Q2(july, aug, sep)

Vol balance    1200

P1 balance      400

for now

and if next Quarter starts i need to show the next Quarter as well.

Please help me in solving this

[t-atgeor]

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]

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

[t-atgeor]

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