How to count things on two different dates

[rpiboy_1]

Let's say that I have an imported semantic model that has the following tables and columns:

• Projects
  • ID
  • Name
  • Created Date
  • Closed Date (calculated column after import)
• Date Table
  • well formed via Bravo
• Projects Closed (Calculated Table)
  • ID (from project)
  • Closed Date (from project)
• Relationships
  • 'Projects'[Created Date] -> 'Date'[Date] (*:1)
  • 'Projects'[ID] -> 'Projects Closed'[ID] (1:1)
  • 'Projects Closed'[Closed Date] -> 'Date'[Date] (*:1 | in-active)
    
    

The calculated table has to be used since with an Import model you can't add a relationship from the Calculated Column to the Date table. Since the Projects and Projects Closed relationship is 1:1 it basically acts as an extension of the Projects Table (great!).

I can write a measure that will then return the count of projects closed forcing the use of the in-active relationship, and therefore get the benifits of the Date table in terms of being able to quantify the count in the context of the date hierarchy (i.e. month, quarter, year, etc...).

What I'd like to do, and I'm not sure what pattern to look for or at is, let's say I want to have a bar chart, where the legend would distuinguish Created versus Closed projects within a given date context. I.E. I want to have Created and Closed function as a dimension to the Projects, so that for a given a month I could easily and graphically see how many projects were created versus how many were closed. Keeping in mind that the project rows do not move in time, rather for each project row it effectively has a Created Date and a Closed Date. I'm not sure how to map this to the classic patterns that are used to describe how to calculate different results (i.e. [Sales Amount], etc.).

[Anonymous]

Hi @rpiboy_1 ,

Thanks for your feedback.

Best regards,

Adamk Kong

[rpiboy_1 · Accepted solution]

Nothing like a good ski trip to help clear the mind and think through the problem.

I realized I needed to get into a context where I had a row for each state. I.E. each project needed a row for created and a row for closed (if it was closed). Then all the rows could have a status column with either the value of 'Created' or 'Closed', thereby giving me my dimension for the legend.

After trying a couple of different things, I ended up with this calculated table, and the performance is pretty good, since it only has a filter and it is not actually calculating any new values.

VAR _closedProj =
    CALCULATETABLE(
		SELECTCOLUMNS(
			Projects,
	        "Date", Projects[Date Closed],
	        "project_id", Projects[region.project_id],
	        "Status", "Closed"
	    ),
	    KEEPFILTERS( TREATAS( {"Closed"}, Projects[dRofus Admin Status] ))
	)

VAR _createdProj =
    SELECTCOLUMNS(
    	Projects,
	    "Date", 'Projects'[Created Date],
	    "project_id", 'Projects'[region.project_id],
        "Status", "Created"
	)

RETURN
UNION(_createdProj, _closedProj)

[rpiboy_1]

The closest other 'business' example I can think of would be subscribing versus un-scribing. If you wanted to track number of new subscrptions, versus closed subscriptions based on your customer data, in which case presumably for each customer there is a 'subscribed' date and an 'un-subscribed' date.

[some_bih]

Hi @rpiboy_1 for part Closed Date, did you try to do creation of calculated column and logic applied in Power Query part?
Check also usage of DAX function USERLATIONSHIP on link and this link.

[rpiboy_1]

I can calculate the count of closed projects with USERELATIONSHIP just fine. The issue is that typically in a bard chart, you have a single calculated value, that you then split into multiple bars based on a dimension. In this case the dimension is Creates or Closed, the status of which is based on the existence of said dates, so I'm counting two different dates on the same set of rows. Projects that are 'closed' will also have a 'created' date, which means they're effectively counted twice, once in each context.