Forum Discussion
Simple noob calulation help
Hi,
I'm a noob on using Power BI and have tried to find a solution for this on my own but without success.
I have one table at the moment with alot of columns where three columns are of importance.
- ID (string)
- Created date (date/time)
- Resolved date (date/time)
I would like to divide the amount of [ID] per [Resolved date] with the amount of [ID] per [Created date] and visualize this result per calender month. How would I go about to achieve this?
Thanks in advance for any help
2 Replies
Magnus_Hedlund You will need a disconnected Date table or a Date table with an active and inactive relationship. If you use a disconnected table you would use the Dates[Date] column in the x-axis of something like a clustered column visual and your 2 measures would be something like this:
Created Measure = VAR __Dates = DISTINCT('Dates'[Date]) VAR __Table = FILTER('Table', [Created date] IN __Dates) VAR __Result = COUNTROWS(__Table) RETURN __Result Resolved Measure = VAR __Dates = DISTINCT('Dates'[Date]) VAR __Table = FILTER('Table', [Resolved date] IN __Dates) VAR __Result = COUNTROWS(__Table) RETURN __ResultNow, if you have a true timestamp of something other than 12:00:00 AM then you might have to do this:
Created Measure = VAR __Dates = DISTINCT('Dates'[Date]) VAR __Table = FILTER('Table', TRUNC([Created date]) IN __Dates) VAR __Result = COUNTROWS(__Table) RETURN __Result Resolved Measure = VAR __Dates = DISTINCT('Dates'[Date]) VAR __Table = FILTER('Table', TRUNC([Resolved date]) IN __Dates) VAR __Result = COUNTROWS(__Table) RETURN __ResultHI Greg_Deckler
Thx for the reply.
This seems complicated...
As the tool can visualize the number of [ID] per [Created date].[Month] and [Resolved date].[Month] respectively I hope there is an easier way because it is basically a divide of those 2 visualization that I am seeking.
