This quarter to last quarter revenue comparison only for lapsed days

[Anonymous]

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

[Jihwan_Kim]

Hi, @Anonymous 

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.

 

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/

[Anonymous]

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]

@Anonymous , 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

[Anonymous]

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