cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
NewStart2020
Frequent Visitor

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

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.

 

IDCategory_NumberEffective_Date
1804/17/2020
2803/10/2020
38012/5/2019
4709/8/2019
5707/13/2019
6703/5/2019
77012/18/2018
8606/1/2018
9603/22/2018
10602/28/2018
116012/1/2017
12509/3/2017
13505/2/2017
14503/31/2017
155012/26/2016
16503/25/2015
17403/22/2016
184012/25/2015
194011/31/2015
20409/30/2015

 

Category_NumberCount_ID_CurrentCount_ID_Previous
8032
7023
6033
5034
404 

 

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! 

 

 

 

 

1 ACCEPTED SOLUTION

Hi @NewStart2020 ,

 

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

 

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

View solution in original post

12 REPLIES 12
harshnathani
Super User III
Super User III

Hi @NewStart2020 ,

 

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

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! 

Hi @NewStart2020 ,

 

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

Capture.JPG

 

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.

 

Capture.JPG

 

Cheers

 

CheenuSing

 

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

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

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! 

 
 

Hi,

 

Try with - DateTime.LocalNow() 

 

Regards,

Avinash

Never mind. 

 

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

 

Thanks again! 

 

Hi @NewStart2020 ,

 

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

 

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

View solution in original post

Fantastic! 

 

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

apentyala
Helper II
Helper II

Hi,

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

 

Regards,

Thanks for your reply! 

Yes, I want to compare based on the Category_Number. 

 

 

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

 

 

 

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

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.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.