QoQ Calculation for Fiscal Quarter

[rohit_sharma2]

Hi, I have fiscal Year/quarter starting from Feb, and in following format : 2018Q1 2018Q2 please help me on QoQ calculation, previous quarter and dateadd function doesnt work on custom calendar which i created according to my organization needs......it should display in following format: Quarer Sales Last Quarter 2018Q1 100 2018Q2 200 100 2018Q3 300 200

[v-lili6-msft · Accepted solution]

hi, @rohit_sharma2

Does your custom calendar have no date column, just like this:

If so, you could try this way as below:

Step1:

Add a rank column for fiscal Year/quarter

rank = RANKX('calendar','calendar'[ fiscal Year/quarter],,ASC )

Note: You could add the rank column for the custom calendar according to the custom.

Step2:

Create a relationship between data table and custom calendar table

Step3:

Use this formula to add a measure

Measure = CALCULATE(SUM('Table'[value]), FILTER(ALL('calendar'),'calendar'[rank]=MAX('calendar'[rank])-1))

Result:

and here is pbix file, please try it.

If it is not your case, please share sample pbix file or some data sample and expected output. Do mask sensitive data before uploading

Best Regards,

Lin

[v-lili6-msft · Accepted solution]

hi, @rohit_sharma2

Does your custom calendar have no date column, just like this:

If so, you could try this way as below:

Step1:

Add a rank column for fiscal Year/quarter

rank = RANKX('calendar','calendar'[ fiscal Year/quarter],,ASC )

Note: You could add the rank column for the custom calendar according to the custom.

Step2:

Create a relationship between data table and custom calendar table

Step3:

Use this formula to add a measure

Measure = CALCULATE(SUM('Table'[value]), FILTER(ALL('calendar'),'calendar'[rank]=MAX('calendar'[rank])-1))

Result:

and here is pbix file, please try it.

If it is not your case, please share sample pbix file or some data sample and expected output. Do mask sensitive data before uploading

Best Regards,

Lin

[rohit_sharma2 · Accepted solution]

Hi Lin,

I tried your solution again, and it worked.

Thank youuuuuuuuuuuuuuuuuuu so muchhhhhhhhhhhhh.

Now other issue is apple to apple comparison, if in current quarter only 6 weeks have passed so wish to compare this with last quarter with 6 weeks data only.....please help me again.......

Regards

Rohit

[v-lili6-msft]

hi, @rohit_sharma2

What the form of week in you data, Please share some data sample and expected output. You can upload it to OneDrive and post the link here. Do mask sensitive data before uploading.

Best Regards,

Lin

[rohit_sharma2]

Hi Lin,

Thank you.

My official work system doesnt allow me to save it on onedrive.

I will do it on my personal systeme and let you know.

One issue with Rankx function is it treats 1,10,11, 12,13  same when ranking the numbers because 10/11/12/13 starts with 1, see below, how to fix this

1---Rank Returned 1

2----6

3----7

4

5

6

7

8

9

10----2

11----3

12----4

13----5

[v-lili6-msft]

hi, @rohit_sharma2

If this column is a text format, then rankx function will return the result like you described.

There are usually two ways:

1. Transform it from text to number

2. add the rank column manually instead of by rankx function.

Best Regards,
Lin

[rohit_sharma2]

Hi Lin,

Please use this link to access sample file...its on same lines on what i have in database.

https://1drv.ms/u/s!AhWQcbvhekwKaf-RtVQA9S-_G3k

I have made custome calendar according to our requirements where :

1. Fiscal Year/1st Quarter Starts from Feb.

2. Fiscal Week Starts from Saturday till Friday.

Need solution on:

1. Calculation Week On Week growth rate, same logic is not happening as done for QoQ.

2. Also need to calculate Apples to Apples comparison for QoQ, for e.g. if current quarter we are in 10th week then it should compare with 10 weeks last quarter.

I am really grateful to you, i am receiving regular replies from you. It has been just 10-15 days i have started learning and using powerbi, i look forwar to learn more from you.

Regards

Rohit

[v-lili6-msft]

hi, @rohit_sharma2

1. you could use this formula add a rank fiscal year week column

rank fiscal year week = RANKX(DateCalendar,DateCalendar[Fin Year]*100+DateCalendar[Fis Week Number],,ASC,Dense)

and use the same logic create a measure:

Last week Sales = CALCULATE([Total_Sales],FILTER(ALL(DateCalendar),DateCalendar[rank fiscal year week]=MAX(DateCalendar[rank fiscal year week])-1))

2. you could add a conditional in the basic formula as below:

NEW Last Qtr Sales = 
CALCULATE (
    [Total_Sales],
    FILTER (
        ALL ( DateCalendar ),
        (
            DateCalendar[rank quarter]
                = MAX ( DateCalendar[rank quarter] ) - 1
        )
            && ( DateCalendar[Week of Quarter] <= MAX ( DateCalendar[Week of Quarter] ) )
    )
)

Best Regards,

Lin

[rohit_sharma2]

1.Hi Lin, Hope you are doing great. I am stuck with how to show Actuals Vs Targets in tabular format , not getting everything correctly, please help me on getting the following view. I have already incorporated day wise targets in calendar and getting accurate results however want the below view: 2. 3. Thankyou very much...Rohit

DEPARTMENT A	Actuals	Targets
Units	900	1,000
Rev (K)	$1,500	$2,000
Rec %	45.0%	50.0%
DEPARTMENT B	Actuals	Targets
Units	900	1,000
Rev (K)	$1,500	$2,000
Rec %	45.0%	50.0%
DEPARTMENT C	Actuals	Targets
Units	900	1,000
Rev (K)	$1,500	$2,000
Rec %	45.0%	50.0%
DEPARTMENT D	Actuals	Targets
Units	900	1,000
Rev (K)	$1,500	$2,000
Rec %	45.0%	50.0%

[rohit_sharma2]

Thank you a ton Lin....you are amazing....appreciate all your help...i will trouble you for more....i am learning a lot from you....

cheers

[rohit_sharma2]

Thank you Lin for quick response, i really appreciate this, however couldnt find the pbbix file but notes were helpful.

The solution is good, however not working for me.

I am unble to attach file as this forum doesnt have any option, kindly suggest me another way or if you can share your email address would be grateful to you.

Best Regards

Rohit

cnda.rohit28@gmail.com