Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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!
Solved! Go to Solution.
Hi @Anonymous ,
Sorry forgot to mention the measure Count Previous
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
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 @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
@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!
Never mind.
I changed "Today" to " DateTime.Date(DateTime.localNow())" , and the problem resolves.
Thanks again!
Hi @Anonymous ,
Sorry forgot to mention the measure Count Previous
Fantastic!
Thank you so much for your solution! It works perfectly!
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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
33 | |
15 | |
14 | |
12 | |
9 |