Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
o59393
Post Prodigy
Post Prodigy

Rolling 12 months vs rolling PY with 1 month lag

Hi all

 

I need to calculate the actual 12 rolling months (CY) versus the previous year (PY) of that 12 month rolling with 1 month lag on both cases.

 

Example. If I select 1/1/2019 from the slicer, then I should get the consumer complaints from 1/1/2018 to 12/1/2018 versus 1/1/2017 to 12/1/2018.

 

I did a dax for the CY called complaints 12M, however it is not doing me the 1 month lag thing. So if I select 1/1/2019, it takes 2/1/2018 to 1/1/2019, which I dont want. (I attach PBIx).

 

The consumer complaints definition is: 

 

Complaints = ((SUM
(complaints[number_complaints])
/
SUM(production[production])))
*VALUE(1000000)

 

 

Is it possible to calculate

 

The complaints KPI for the CY and PY 

 

And to validate the KPI's:  

 

The number of complaints for the CY and PY 

The production for the the CY and PY 

 

Thanks a mill!!

2 ACCEPTED SOLUTIONS

Hey @o59393 

 

I changed the measure "Measure PY" to this:

 

Measure PY = 
var SelectedDate = CALCULATE(MAX('date'[date]))
var SelectedDateYear = YEAR(SelectedDate)
var SelectedDateMonth = MONTH(SelectedDate)
var SelectedDateDay = DAY(SelectedDate)
var RangeStart = DATE(SelectedDateYear - 2 , SelectedDateMonth , 1)
var PreviousMonthEndOf = DATE(SelectedDateYear , SelectedDateMonth , 1) - 1 //end of previous month
var PreviousMonthYear = YEAR(PreviousMonthEndOf)
var PreviousMonthMonth = MONTH(PreviousMonthEndOf)
var PreviousMonthDay = DAY(PreviousMonthEndOf)
var RangeEnd = DATE(PreviousMonthYear - 1 , PreviousMonthMonth , PreviousMonthDay) //end of previous month one year ago, a leap year is not considered
return
 
CALCULATE(
    [Complaints]
    , DATESBETWEEN('date'[date] , RangeStart , RangeEnd)
)

 

The change is here:

var RangeStart = DATE(SelectedDateYear - 2 , SelectedDateMonth , 1)

 

Now values start to appear, as there will be complaints:

image.png

 

Due to the typo "-1" instead of "-2" the date of the variable RangeStart was greater than the date of the variable RangeEnd, unfortunately this led to an empty table returned by the function DATESBETWEEN(...) 

 

Regards,

Tom

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

Hey @o59393 ,

 

I just edited the PY DAX, the keyword return was missing between the CALCULATE and the last var definition.

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

20 REPLIES 20
TomMartens
Super User
Super User

Hey @o59393 ,

 

I have to admit that I my difficulties to understand what exactly are the dateframes, please specifiy the dateframes using the ISO-format like so

Slicer Selection 2019-01-01 (January, 1st 2019)

--> daterange CY 2018-01-01 - ...

--> daterange PY 

 

By the you mention the selection of a specific date from the slicer, but your pbix contains a date range slicer, how does this fit?

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Hi Tom

By the iso format (yyyy/mm/dd) it would be

Select 2019/01/01

CY: 2018/01/01 to 2018/12/01
PY: 2017/01/01 to 2017/12/01

Other example is

Select 2019/09/01

CY 2018/09/01 to 2019/08/01
PY 2017/09/01 to 2018/08/01

About the slicer you are correct. I had used a data range to validate the right table, but it should be a dropdown. Please ommit the data range.

Please let me know if I clarify Tom.

Danke!

Hey @o59393 ,

 

thanks for the clarification, now my understandig is almost complete. From the examples you have been given it's more 11 months than 12 months right?

 

What is the date range, if 2019-10-26 (October, 26th 2019) is selected?

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Hi Tom

 

It would be 12 months. See this example (with random values I used):

 

rolling.PNG

 

The CY in yellow is composed of the sum of complaints for the 12 months and the sum of the 12 months of production. Works the same with the PY.

 

For  2019-10-26 (October, 26th 2019) you mention. The day should not matter, only the month and year.

 

Therefore,

CY: 2018/10/01 to 2019/09/01

PY 2017/10/01 to 2018/09/01

 

Hope this clarifies.

 

Thanks!

 

 

 

I think I got your point. When I said in the example

"Therefore,

CY: 2018/10/01 to 2019/09/01

PY 2017/10/01 to 2018/09/01
"

Both septembers for cy and py must include all the data fot these months too.

Thanks.

Hey @o59393 ,

 

the problem is to compose the proper timeframe to filter the days from the date table accordingly, this is due to the fact that some DAX functions expect a column reference and raise an error if just a date literal is passed to these functions. For this the code for the two measures may look a little lengthy 🙂

 

The measure CY

Measure CY = 
var SelectedDate = CALCULATE(MAX('date'[date]))
var SelectedDateYear = YEAR(SelectedDate)
var SelectedDateMonth = MONTH(SelectedDate)
var SelectedDateDay = DAY(SelectedDate)
var RangeStart = DATE(SelectedDateYear - 1 , SelectedDateMonth , 1)
var RangeEnd = DATE(SelectedDateYear , SelectedDateMonth , 1) - 1 //end of previous month
return 
CALCULATE(
    [Complaints]
    , DATESBETWEEN('date'[date] , RangeStart , RangeEnd)
)

The measure PY, be aware that a leap year is not considered. This means that you have to adjust the formula to determine the proper RangeEndDate if you select 2016-03-01

Measure PY = 
var SelectedDate = CALCULATE(MAX('date'[date]))
var SelectedDateYear = YEAR(SelectedDate)
var SelectedDateMonth = MONTH(SelectedDate)
var SelectedDateDay = DAY(SelectedDate)
var RangeStart = DATE(SelectedDateYear - 1 , SelectedDateMonth , 1)
var PreviousMonthEndOf = DATE(SelectedDateYear , SelectedDateMonth , 1) - 1 //end of previous month
var PreviousMonthYear = YEAR(PreviousMonthEndOf)
var PreviousMonthMonth = MONTH(PreviousMonthEndOf)
var PreviousMonthDay = DAY(PreviousMonthEndOf)
var RangeEnd = DATE(PreviousMonthYear - 1 , PreviousMonthMonth , PreviousMonthDay) //end of previous month one year ago, a leap year is not considered
return 
CALCULATE(
    [Complaints]
    , DATESBETWEEN('date'[date] , RangeStart , RangeEnd)
)

 

Hopefully this provides what you are looking for.

 

Regards,

Tom

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Hi @TomMartens 

 

Any news on how to get the PY metric?

 

Regards!

Hey @o59393 

 

I changed the measure "Measure PY" to this:

 

Measure PY = 
var SelectedDate = CALCULATE(MAX('date'[date]))
var SelectedDateYear = YEAR(SelectedDate)
var SelectedDateMonth = MONTH(SelectedDate)
var SelectedDateDay = DAY(SelectedDate)
var RangeStart = DATE(SelectedDateYear - 2 , SelectedDateMonth , 1)
var PreviousMonthEndOf = DATE(SelectedDateYear , SelectedDateMonth , 1) - 1 //end of previous month
var PreviousMonthYear = YEAR(PreviousMonthEndOf)
var PreviousMonthMonth = MONTH(PreviousMonthEndOf)
var PreviousMonthDay = DAY(PreviousMonthEndOf)
var RangeEnd = DATE(PreviousMonthYear - 1 , PreviousMonthMonth , PreviousMonthDay) //end of previous month one year ago, a leap year is not considered
return
 
CALCULATE(
    [Complaints]
    , DATESBETWEEN('date'[date] , RangeStart , RangeEnd)
)

 

The change is here:

var RangeStart = DATE(SelectedDateYear - 2 , SelectedDateMonth , 1)

 

Now values start to appear, as there will be complaints:

image.png

 

Due to the typo "-1" instead of "-2" the date of the variable RangeStart was greater than the date of the variable RangeEnd, unfortunately this led to an empty table returned by the function DATESBETWEEN(...) 

 

Regards,

Tom

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

 Hi Tom

 

Appreciate the help, it worked. Now one final question, just to understand the DAX. I marked in red why I think is each line for. Could you please clarify, this will help me a lot.

 

Measure PY =
var SelectedDate = CALCULATE(MAX('date'[date])) // is the max to return one date?
var SelectedDateYear = YEAR(SelectedDate) // is this for the slicer?
var SelectedDateMonth = MONTH(SelectedDate)
var SelectedDateDay = DAY(SelectedDate)
var RangeStart = DATE(SelectedDateYear - 2 , SelectedDateMonth , 1) //Returns the start date year 2 years from what I select and the 1 is for?
var PreviousMonthEndOf = DATE(SelectedDateYear , SelectedDateMonth , 1) - 1 //end of previous month // the positive 1 is for?
var PreviousMonthYear = YEAR(PreviousMonthEndOf) //why previous month end of in parenthesis?
var PreviousMonthMonth = MONTH(PreviousMonthEndOf)
var PreviousMonthDay = DAY(PreviousMonthEndOf)
var RangeEnd = DATE(PreviousMonthYear - 1 , PreviousMonthMonth , PreviousMonthDay) //end of previous month one year ago, a leap year is not considered
return
 
CALCULATE(
[Complaints]
, DATESBETWEEN('date'[date] , RangeStart , RangeEnd)
)

 

Thanks again!

Hey @o59393 ,

 

this 

var SelectedDate = CALCULATE(MAX('date'[date])) // is the max to return one date?

You are right MAX is used to return one vlaue, the same column should be used that is used to feed the slicer. This stores the seleceted date from the slicer to the variable SelectedDate. If no date is selected (marked) on the slicer the last available date (MAX) will be used. If a different column from the 'date" table is used inside the slicer, MAX determines the MAX filtered [date] value.

 

this
var SelectedDateYear = YEAR(SelectedDate) // is this for the slicer?

just derives the YEAR from the variable SelectedDate, this only works if the column 'date'[date] is of the datatype date or datetime


this
var RangeStart = DATE(SelectedDateYear - 2 , SelectedDateMonth , 1) //Returns the start date year 2 years from what I select and the 1 is for?

The function DATE has three numeric parameters (year, month, day), this means 1 marks the 1st of the month

 

this
var PreviousMonthEndOf = DATE(SelectedDateYear , SelectedDateMonth , 1) - 1 //end of previous month // the positive 1 is for?

The same as above, the positive 1 is the 1st of a month, the "simple" date arithmetic supported by DAX allows to subtract or add any integer from a column of datatype date, in this case subtracting -1 from a date that looks liks this 2019-10-01 or DATE(2019 , 10 , 1) returns a that represents the last of the previous monht. The result of DATE(2019 , 10 , 1) -1 will be September, 30th 2019. This is stored to the variable PreviousMonthEndOf

 

this
var PreviousMonthYear = YEAR(PreviousMonthEndOf) //why previous month end of in parenthesis?

The DAX function YEAR extraxt the year value from the date stored to the variable PreviousMonthEndOf.

 

Hopefully this adds some additional insights.

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

hi @TomMartens 

 

Me again. I am using these dax to calculate a 2 month lag instead of one for another metric. I placed a -2 instead of a -1 as seen below. However is not returning me the 2 month lag, in fact still returns a 1 month lag (as seen in the image). 

Measure CY = 
var SelectedDate = CALCULATE(MAX('date'[date]))
var SelectedDateYear = YEAR(SelectedDate)
var SelectedDateMonth = MONTH(SelectedDate)
var SelectedDateDay = DAY(SelectedDate)
var RangeStart = DATE(SelectedDateYear - 1 , SelectedDateMonth , 1)
var RangeEnd = DATE(SelectedDateYear , SelectedDateMonth , 1) - 2 //¿end of previous 2 months?
return 
CALCULATE(
    [Complaints]
    , DATESBETWEEN('date'[date] , RangeStart , RangeEnd)
)

 

Measure PY = 
var SelectedDate = CALCULATE(MAX('date'[date]))
var SelectedDateYear = YEAR(SelectedDate)
var SelectedDateMonth = MONTH(SelectedDate)
var SelectedDateDay = DAY(SelectedDate)
var RangeStart = DATE(SelectedDateYear - 2 , SelectedDateMonth , 1)
var PreviousMonthEndOf = DATE(SelectedDateYear , SelectedDateMonth , 1) - 2 //¿end of previous 2 months?
var PreviousMonthYear = YEAR(PreviousMonthEndOf)
var PreviousMonthMonth = MONTH(PreviousMonthEndOf)
var PreviousMonthDay = DAY(PreviousMonthEndOf)
var RangeEnd = DATE(PreviousMonthYear - 1 , PreviousMonthMonth , PreviousMonthDay) //end of previous month one year ago, a leap year is not considered
return
 
CALCULATE(
    [Complaints]
    , DATESBETWEEN('date'[date] , RangeStart , RangeEnd)
)

Same results despite using a -2:

2py.PNG

 

How can I get it ok?

 

Thanks!

Hey @o59393 ,

 

please provide an example what date should be considered for your 2 month lag, if

2019-01-01 (1st of January 2019) is selected and what is the date range if 2019-09-01 (1st of September 2019) is selected?

 

This line substracts just 2 days from the date DATE(year , month , day):

var RangeEnd = DATE(SelectedDateYear , SelectedDateMonth , 1) - 2

This means

DATE(2019 , 9 , 1) - 2

results to 30th of August 2019 

 

Regards,

Tom 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Greetings @TomMartens ! Happy new year.

 

Do you know how I can get the 2 month lag? please let me know if the example I did was ok.

 

Regards!

Hey @o59393 ,

 

this returns the filter for the CY 2 months lag:

 

 

Measure CY 2MonthsLag = 
var SelectedDate = CALCULATE(MAX('date'[date]))
var DateCYLeft = EDATE(SelectedDate, -13)
var DateCYLeftYear = YEAR(DateCYLeft)
var DateCYLeftMonth = MONTH(DateCYLeft)
Var DateCYStart = DATE(DateCYLeftYear , DateCYLeftMonth , 1)
var DateCYRight = EDATE(SelectedDate , -1)
var DateCYRightYear = YEAR(DateCYRight)
var DateCYRightMonth = MONTH(DateCYRight)
var DateCYEnd = DATE(DateCYRightYear , DateCYRightMonth , 1) - 1
return 
CALCULATE(
    [Complaints]
    , DATESBETWEEN('date'[date] , DateCYStart , DateCYEnd)
)

 

 

 

And this for the PY 2 months lag

 

 

Measure PY 2MonthsLag = 
var SelectedDate = CALCULATE(MAX('date'[date]))
var DatePYLeft = EDATE(SelectedDate, -26)
var DatePYLeftYear = YEAR(DatePYLeft)
var DatePYLeftMonth = MONTH(DatePYLeft)
Var DatePYStart = DATE(DatePYLeftYear , DatePYLeftMonth , 1)
var DatePYRight = EDATE(SelectedDate , -13)
var DatePYRightYear = YEAR(DatePYRight)
var DatePYRightMonth = MONTH(DatePYRight)
var DatePYEnd = DATE(DatePYRightYear , DatePYRightMonth , 1) - 1
return
CALCULATE(
    [Complaints]
    , DATESBETWEEN('date'[date] , DatePYStart , DatePYEnd)
)

 

 

 

Hopefully this is what you are looking for.

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Hi Tom

 

I got an error with the PY one:

 

2month.png

Did I miss something?

 

Thanks!

 

Hey @o59393 ,

 

I just edited the PY DAX, the keyword return was missing between the CALCULATE and the last var definition.

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Hi.

 

For 2019/09/01 slicer's selection with 2 month lag:

 

The CY 12 month rolling:

August 2018 to July 2019

 

The PY 12 month rolling:

August 2017 to July 2018

 

With the 1 month lag you already have calculated for september 2019 is:

CY: September 2018 - August 2019

PY: September 2017 - August 2018

 

Let me know if it's ok.

 

Thanks!

Hi,

Try this measure for CY 12 month rolling

=CALCULATE([Complaints],DATESBETWEEN('date'[date],EDATE(MIN('date'[date]),-12),EDATE(MIN('date'[date]),-1)-1))

Try this measure for PY 12 month
rolling

=CALCULATE([Complaints],DATESBETWEEN('date'[date],EDATE(MIN('date'[date]),-24),EDATE(MIN('date'[date]),-12)-1))

Hope this helps.

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Thanks a mill Tom.

 

Have a nice day!

Hi Tom

 

Appreciate the help. I could have never done something like this 😮

 

I see you used a  "var SelectedDateDay = DAY(SelectedDate)", but if I am correct, this is not needed for the CY, since I removed it and it still worked.

 

However for PY you do need it: var RangeEnd = DATE(PreviousMonthYear - 1 , PreviousMonthMonth , PreviousMonthDay). What is the purpose of the previousMonthDay, can it just be the year and month?

 

I calculated with your dax the CY and PY for the rolling number of complaints and the production. This will give me more visibiliity. 

 

Thanks!!!!!

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.