cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
HSubbaiah
Helper III
Helper III

This quarter to last quarter revenue comparison only for lapsed days

Hi All,

 

I am looking for quarter on quarter comparison for the revenue I have made:

There is a quarter filter and the output should change according to the selection made.

 

For example:

Today we are in QTR 2 2021

So when I select QTR 2 2021 from the slicer the data from 1st April 2021 till 15th April 2021 should be compared with 1st Jan 2021 to 15th Jan 2021.

 

But when I select Qtr 1 2021 then data from jan 1st 2021 till march 31st 2021 should be compared to whole of Qtr 4 2020 because the QTR 1 2021 is completed already.

 

Year

Quarter

Total_Revenue

Last Quarter Data

2019

Qtr 3 2019

152855

 

2019

Qtr 4 2019

238609

152855

2020

Qtr 1 2020

352

238609

2020

Qtr 2 2020

677

352

2020

Qtr 4 2020

15700

677

2021

Qtr 1 2021

19916

15700

2021

Qtr 2 2021

2698

1000

 

 

Here we see full of QTR 1 2021 its 19916 but it should show only for the number of days completed in QTR 2 2021, 15 days completed in QTR 2 2021 so only 15 days revenue from QTR 1 2021 should appear here.

 

I can get this value 1000 but the others are coming wrong.

 

What I did :

Total_Revenue:= sum(Revenue [Revenue])

LASTDATEthisyear:=CALCULATE(MAX(revenue[Date]),ALL(revenue[Date]))

From this I am getting the last date there is revenue in the data model which will be yesterday.

 

 

First month of the last quarter:= SWITCH(ROUNDUP ( DIVIDE ( MONTH ( [LASTDATEthisyear] ),3 ),0 ) *3 -2,1,10,2,11,3,12,4,01,5,02,6,03,7,04,8,05,9,06,10,07,11,08,12,09)

Used this to get the month of the last quarter in comparison to the current month this gives me 1 (jan)

 

End_Quarter:=DATE (year([LASTDATEthisyear]),[ First month of the last quarter] , DAY([LASTDATEthisyear]))

This gives me (1/14/2021)

 

Start_Quarter:=eomonth( [End_Quarter],-1 )+1

This gives me (1/1/2021)

 

RAG_Quarter:=VAR first_date =

    eomonth( [End_Quarter],-1 )+1

VAR last_date =

  DATE (year([LASTDATEthisyear]),[ First month of the last quarter] , DAY([LASTDATEthisyear]))

RETURN

    IF (

        ISBLANK ( first_date ) || ISBLANK ( last_date ),

        BLANK (),

        CALCULATE (

            [Total_Revenue],

            DATESBETWEEN ( 'Calendar'[Date],first_date,last_date)))

 

 

Thanks in Advance

hema

4 REPLIES 4
Jihwan_Kim
Community Champion
Community Champion

Hi, @HSubbaiah 

Please correct me if I wrongly understood your question.

Please check the below picture and the sample pbix file's link down below, whether it is what you are looking for.

 

all measures are in the sample pbix file.

 Picture4.png

 

https://www.dropbox.com/s/qn4zt2enfykxw3n/hsubbaiah.pbix?dl=0 

 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: https://www.linkedin.com/in/jihwankim1975/


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Best regards, JiHwan Kim

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

Hi Jihwan_Kim,

Thanks for taking time but the data i am expecting is diffrent.

Here i see that previous quarter sales for QTR 2 2021 is showing sum of sales of QTR 1 2021.

but what i am looking for is : 

in QTR 2 2021 15 days are completed so it doesn’t make sense in comparing whole of last quarter with only 15 days of this quarter. 

so when I select QTR 2 need to compare 15 days of QTR 1 2021 ie sales from 1/1/2021  to 15/1/2021 with QTR 2 2021.

But when I click on QTR 1 2021 since it is completed quarter then we need to compare the data of Qtr 4 2020 with Qtr 1 2021.

As per the data in the sales table you have shared the expected output against QTR 2 2021 is 392593

i have summed the data from 1/1/2021 till 15/1/2021

this adds more value as you see that the data is almost same, we can say compared to last quarter this quarter we have still not reached the same sales.

 Regards,

Hema

amitchandak
Super User IV
Super User IV

@HSubbaiah , if  a  date is selected, the datesqtd should help you with date table

 

examples

QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))
Last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,QUARTER)))

 

Power BI — Qtr on Qtr with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
https://www.youtube.com/watch?v=8-TlVx7P0A0



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Hi Amit,

This is also is giving me full QTR 1 2021 (sum of jan,feb,mar 2021) data as Last QTD Sales against Qtr 2 2021.

But in QTR 2 2021 we have completed only 15 days so we cannot compare the whole of last month data with 15 days of this month.

 

So Last QTD Sales  for QTR 2 2021 should sum up data from 1/1/2021 till 15/1/2021.

For QTR 1 2021 since the whole quarter is complete we need to show 

Last QTD Sales for QTR 1 2021 sum of sales of Oct , Nov,Dec 2020 and so on.

 

Regards,

Hem

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors