topic Today compare with the same day last year, 2/3/4/5 years ago in DAX Commands and Tips

Today compare with the same day last year, 2/3/4/5 years ago

Anonymous — Fri, 17 Apr 2020 16:11:04 GMT

Hi everyone,

I'm new to Power BI and DAX.

I want to show the total ID count of this year and the total ID counts of previous years at the same dates each of which is associated with a different Category_Number.

The below tables show the original data set (1st table) and the result table (2nd table) I would like to have.

ID	Category_Number	Effective_Date
1	80	4/17/2020
2	80	3/10/2020
3	80	12/5/2019
4	70	9/8/2019
5	70	7/13/2019
6	70	3/5/2019
7	70	12/18/2018
8	60	6/1/2018
9	60	3/22/2018
10	60	2/28/2018
11	60	12/1/2017
12	50	9/3/2017
13	50	5/2/2017
14	50	3/31/2017
15	50	12/26/2016
16	50	3/25/2015
17	40	3/22/2016
18	40	12/25/2015
19	40	11/31/2015
20	40	9/30/2015

Category_Number	Count_ID_Current	Count_ID_Previous
80	3	2
70	2	3
60	3	3
50	3	4
40	4

Today's date is 4/17/2020, and the date changes every day.

For Category_Nbr = 80: Effective_Date <Today

For Category_Nbr = 70: Effective_Date < Last year Today

For Category_Nbr = 60: Effecitve_Date < 2 years ago Today

For Category_Nbr = 50: Effecitve_Date < 3 years ago Today

For Category_Nbr = 40: Effecitve_Date < 4 years ago Today

Any ideas or suggestions will be greatly appreciated.

Thanks!

Re: Today compare with the same day last year, 2/3/4/5 years ago

apentyala — Fri, 17 Apr 2020 16:50:37 GMT

Hi,

please provide more details, you want to compare based category_number?

Regards,

Re: Today compare with the same day last year, 2/3/4/5 years ago

Anonymous — Fri, 17 Apr 2020 17:04:05 GMT

Thanks for your reply!

Yes, I want to compare based on the Category_Number.

Re: Today compare with the same day last year, 2/3/4/5 years ago

Anonymous — Sat, 18 Apr 2020 20:32:36 GMT

Below are two measures I created.

The Total_ID measure works well. It shows the total number of IDs that have the Category_Number =80 and Effecitve_Date < Today.

The Total_ID_LY measure shows the total number IDs that have the Category_Number = 70, but doesn't show the Effective_Date< The same data last year.

I want the Total_ID_LY measure to have two filters, Category_Number = 70 (80-10) and Effective_Date < The same data last year of today.

Any helps?

Thanks!

Total_ID = COUNT('myTable'[ID])

Total_ID_LY =
VAR CurrentCategory = SELECTEDVALUE ( 'myTable'[Category_Number] )
VAR PreviousCategory =
CALCULATE (
MAX ( 'myTable'[Category_Number] ),
ALLSELECTED ( 'myTable' ),
KEEPFILTERS ( 'myTable'[Category_Number] < CurrentCategory )
)
VAR Result =
CALCULATE (
[Total_ID],
'myTable'[Category_Number] = PreviousCategory
)
RETURN
Result

Re: Today compare with the same day last year, 2/3/4/5 years ago

Anonymous — Sun, 19 Apr 2020 14:18:30 GMT

Hi Anonymous ,

Can you please confirm the output.

For Category 70, Count Current_Id should be 4 and Count_Id Previous should be 2.

Is this correct or can you explain it a little better?

Regard,

Harsh Nathani

Re: Today compare with the same day last year, 2/3/4/5 years ago

Anonymous — Mon, 20 Apr 2020 01:05:40 GMT

Thanks for your reply!

The Count_Current_ID is 2 for Category_Number 70 because only 2 IDs meet the requirements (Category_Number = 70 and Effective_Date < 4/18/2029).

The Count_Previous_ID is 3 for Category_Number 60 because only 3 IDs meet the requirements (Category_Number = 60 and Effective_Date < 4/18/2018).

Hope the above explanations make sense. Thanks!

Re: Today compare with the same day last year, 2/3/4/5 years ago

Anonymous — Mon, 20 Apr 2020 05:26:44 GMT

Hi Anonymous ,

What you need to do is create a column using powerquery , the date value to compare depending on the category number and the number of years to go back.

The sample data with transormation

The Power query is as under

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc/LEQQhCATQXDxPFT9FjWVq8k9jQWXl4uG1tvC+hcpTBtpRgTowMpbveQsHCxBelmBiaMY0F1eT7jxhXG2hHUgua7Dkih5qzbRKxvJhpO4KdHWGWi9fJgw3TCVE4d7u3rf7mm0PLokl2AZMXIMFJLe0cGtn9UB3oPcBr13bdl+25vHP/RHuRfnB/Ad0vt4BYwS2AB7/fg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Category_Number = _t, Effective_Date = _t]), #"Replaced Value" = Table.ReplaceValue(Source,"11/31/2015","11/30/2015",Replacer.ReplaceText,{"Effective_Date"}), #"Changed Type with Locale" = Table.TransformColumnTypes(#"Replaced Value", {{"Effective_Date", type date}}, "en-US"), Today = DateTime.LocalNow(), #"Added Conditional Column" = Table.AddColumn(#"Changed Type with Locale", "DaytoCompare", each if [Category_Number] = "80" then Today else if [Category_Number] = "70" then Date.AddYears(Today ,-1) else if [Category_Number] = "60" then Date.AddYears(Today ,-2) else if [Category_Number] = "50" then Date.AddYears(Today ,-3) else Date.AddYears(Today, -4) ), #"Changed Type" = Table.TransformColumnTypes(#"Added Conditional Column",{{"DaytoCompare", type date}}) in #"Changed Type"let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc/LEQQhCATQXDxPFT9FjWVq8k9jQWXl4uG1tvC+hcpTBtpRgTowMpbveQsHCxBelmBiaMY0F1eT7jxhXG2hHUgua7Dkih5qzbRKxvJhpO4KdHWGWi9fJgw3TCVE4d7u3rf7mm0PLokl2AZMXIMFJLe0cGtn9UB3oPcBr13bdl+25vHP/RHuRfnB/Ad0vt4BYwS2AB7/fg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Category_Number = _t, Effective_Date = _t]), #"Replaced Value" = Table.ReplaceValue(Source,"11/31/2015","11/30/2015",Replacer.ReplaceText,{"Effective_Date"}), #"Changed Type with Locale" = Table.TransformColumnTypes(#"Replaced Value", {{"Effective_Date", type date}}, "en-US"), Today = DateTime.LocalNow(), #"Added Conditional Column" = Table.AddColumn(#"Changed Type with Locale", "DaytoCompare", each if [Category_Number] = "80" then Today else if [Category_Number] = "70" then Date.AddYears(Today ,-1) else if [Category_Number] = "60" then Date.AddYears(Today ,-2) else if [Category_Number] = "50" then Date.AddYears(Today ,-3) else Date.AddYears(Today, -4) ), #"Changed Type" = Table.TransformColumnTypes(#"Added Conditional Column",{{"DaytoCompare", type date}}) in #"Changed Type"

What the query does it captures the todays date and then in the step Additional Conditional Column create a data column to set the date to compare by category.

The sample ouput with the above data done today.

Cheers

CheenuSing

Re: Today compare with the same day last year, 2/3/4/5 years ago

Anonymous — Mon, 20 Apr 2020 05:30:26 GMT

Hi Anonymous ,

Sorry forgot to mention the measure Count Previous

CountPrevious =

var SelCategory = MAX(SampleData[Category_Number])

return

CALCULATE(COUNTROWS(SampleData), Filter((SampleData),SampleData[Effective_Date] <= SampleData[DaytoCompare] &&

SampleData[Category_Number] = SelCategory ) )

CheenuSing

Re: Today compare with the same day last year, 2/3/4/5 years ago

Anonymous — Mon, 20 Apr 2020 13:15:54 GMT

Fantastic!

Thank you so much for your solution! It works perfectly!

Re: Today compare with the same day last year, 2/3/4/5 years ago

Anonymous — Mon, 20 Apr 2020 14:38:36 GMT

One more question about this Step in your Power Query.

Is the following step creating a new "Today" column?

Today = DateTime.LocalNow(),

I created a new "Today" Column by using "Today = Date.From(DateTime.LocalNow())" and it works.

Then I changed the "Today" type to "Date" and created the "DaytoCompare" column according to your suggestions.

But the "DaytoCompare" shows "Error" values for each row.

Do you have any suggestions? Thanks!

Re: Today compare with the same day last year, 2/3/4/5 years ago

Anonymous — Mon, 20 Apr 2020 14:56:46 GMT

Never mind.

I changed "Today" to " DateTime.Date(DateTime.localNow())" , and the problem resolves.

Thanks again!

Re: Today compare with the same day last year, 2/3/4/5 years ago

apentyala — Wed, 22 Apr 2020 17:09:11 GMT

Hi,

Try with - DateTime.LocalNow()

Regards,

Avinash

Re: Today compare with the same day last year, 2/3/4/5 years ago

apentyala — Wed, 22 Apr 2020 17:22:23 GMT

Anonymous Noticed that total are showing wrong for the previous count? did you fix that?