Forum Discussion
How Create Graph with Date difference and age bucket
Hi nish18_1990,
Thank you for using Microsoft Community Forum.
Below is the approach to achieving your desired bar chart visualization in Power BI based on your sample data.
Open Power BI Desktop, go to Home > Get Data, choose Excel or CSV, select your file, and click Load to import the data.
To calculate age, create a new column in Power BI using this DAX formula:
Age = DATEDIFF(TODAY(), 'Sheet1'[Created date], DAY)
This will give the number of days between today and the created date.To group records by age range, create a new column using this DAX formula:
Bucket =
IF('Sheet1'[Age] < 30, "<30",
IF('Sheet1'[Age] <= 60, "<60",
IF('Sheet1'[Age] <= 90, ">30 - 60",
">60"
)
)
)
This will categorize each record into buckets like <30, <60, >30 - 60, and >60 based on the age in days.
To create the bar chart, go to the Report View in Power BI and select the Clustered Column Chart from the Visualizations pane. Drag Created date to the X-axis, Sum of Quantity Q2 to the Y-axis, and Bucket to the Legend. Then, format the chart by sorting it by Created Date, adjusting the colors for each bucket, and renaming the chart to "Sum of Quantity Q2 by Created Date and Age Bucket.
If this solution worked for you, kindly mark it as Accept as Solution and feel free to give a Kudos, it would be much appreciated!
Thank you.
Hi nish18_1990,
Thank you for using Microsoft Community Forum.
Below is the approach to achieving your desired bar chart visualization in Power BI based on your sample data.
Open Power BI Desktop, go to Home > Get Data, choose Excel or CSV, select your file, and click Load to import the data.
To calculate age, create a new column in Power BI using this DAX formula:
Age = DATEDIFF(TODAY(), 'Sheet1'[Created date], DAY)
This will give the number of days between today and the created date.
To group records by age range, create a new column using this DAX formula:
Bucket =
IF('Sheet1'[Age] < 30, "<30",
IF('Sheet1'[Age] <= 60, "<60",
IF('Sheet1'[Age] <= 90, ">30 - 60",
">60"
)
)
)
This will categorize each record into buckets like <30, <60, >30 - 60, and >60 based on the age in days.
To create the bar chart, go to the Report View in Power BI and select the Clustered Column Chart from the Visualizations pane. Drag Created date to the X-axis, Sum of Quantity Q2 to the Y-axis, and Bucket to the Legend. Then, format the chart by sorting it by Created Date, adjusting the colors for each bucket, and renaming the chart to "Sum of Quantity Q2 by Created Date and Age Bucket.
If this solution worked for you, kindly mark it as Accept as Solution and feel free to give a Kudos, it would be much appreciated!
Thank you.
Hi nish18_1990,
We haven’t heard from you on the last response and was just checking back to see if your query was answered.
Otherwise, will respond back with the more details and we will try to help .If our response has addressed your query, please accept it as a solution and give a ‘Kudos’ so other members can easily find it. Please let us know if there’s anything else we can do to help.
Thank you.