Rolling 12 months vs rolling PY with 1 month lag

[o59393]

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!!

[TomMartens · Accepted solution]

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:

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

[TomMartens]

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

[o59393]

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!

[TomMartens]

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

[o59393]

Hi Tom

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

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!

[o59393]

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.

[TomMartens]

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

[o59393]

Hi @TomMartens 

Any news on how to get the PY metric?

Regards!

[TomMartens · Accepted solution]

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:

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

[o59393]

 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!

[TomMartens]

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

[o59393]

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:

How can I get it ok?

Thanks!

[TomMartens]

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 

[o59393]

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!

[TomMartens]

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

[o59393]

Hi Tom

I got an error with the PY one:

Did I miss something?

Thanks!

[TomMartens · Accepted solution]

Hey @o59393 ,

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

Regards,

Tom

[o59393]

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!

[Ashish_Mathur]

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.

[o59393]

Thanks a mill Tom.

Have a nice day!

[o59393]

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!!!!!