dashboard with dynamic date

[blabla702]

Hi, I'm new to BI.

Based on an Excel datasheet (from Jira ticket system), with columns: person, issue ID, resolution date, creation date - I would need to create a simple interactive dashboard.

It should include

• Interactive chart for selecting the year and month
• 2-column histogram relating to the selected time:
  X-axis: person1; person2,
  Y-axis: count of closed issue IDs

Can anyone advise how to proceed?

(I only find a too simple, too complex, or unrelated tutorials)

Thanks in advance

[blabla702]

Wonderfull response and also the project you sent, it's so helpful! I will take the time to look at the tutorials, thank you ❤️

Would it perhaps be possible to calculate the average of the count for the first two people?
And instead of havig the original column name, replace it in 'department average' label?

Thanks in advance!

[Ritaf1983]

Hi @blabla702 

Unfortunately, I'm having trouble understanding what you're trying to do... Please show the desired result.

[blabla702]

Hi again, I would like to add a column that calculates the average closed issue IDs, for all employees.
My question would be if it is possible if it can be done at the BI level instead of Exel..?

[Ashish_Mathur]

Hi,

From what i read, the creation date is of no use.  So create a Calendar Table with calculated column formulas for Year, Month name and Month number.  Sort the Month name column by the Month number.  Create a relationship (Many to One and Single) from the resolution date to the date column of the calendar table.  To any vsual, drag Year and Month name from the Calendar table. 

[Ritaf1983 · Accepted solution]

Hi @blabla702 
The quick and dirty solution is to create resolution year and resolution month calculated columns :

Use these 2 columns as slicers:

And create a column chart with distinctcount aggregation:

The pbix is attached

The most efficient approach is to learn about Star Schema, work with a calendar table, and master DAX.

I've attached links to high-quality learning materials, as it's impossible to summarize the essence of working with the tool in a few sentences within a forum discussion.

Star schema:

https://www.youtube.com/watch?v=7ciFtfi-kQs

The creation of the dim date guide includes the following scripts :

https://radacad.com/create-a-date-dimension-in-power-bi-in-4-steps-step-1-calendar-columns

The dax basics 
https://www.youtube.com/watch?v=QJw4HkagVWc

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.