Forum Discussion

rpiboy_1's avatar
rpiboy_1
Icon for Helper V rankHelper V
1 year ago
Solved

How to count things on two different dates

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 for...
  • rpiboy_1's avatar
    rpiboy_1
    1 year ago

    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's avatar
rpiboy_1
Icon for Helper V rankHelper V
1 year ago

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.