Calculating Active

[carlovskyit]

Hello,

I need to create DAX Measures to find the open Campaigns at Day Level.

T1 has information about(campaign_id,dat_start,dat_end).

Output desired

Simple measure that can retrieve in a card the  # Open Campaigns based on current selections and in a matrix at day level.

Assumptions:

open campaigns:
where starting date might be inferior to actual date in the context of the table and close date is null or higher than actual date in context.                       

Excel file https://we.tl/t-fbTANLetVb 

Thanks a lot

Diego

[ValtteriN · Accepted solution]

Hi,

Here is one way to do this:

Data:

Dax:

Open count =

var _calendarDate = MAX('Calendar'[Date]) //filtered calendar date. There is a relationship between calendar and start date

var _table = ADDCOLUMNS('Table (15)',"open",IF(IF(ISBLANK('Table (15)'[dat_end]),_calendarDate+1,'Table (15)'[dat_end])>_calendarDate,1,0))

return

SUMX(_table,[open])

End result:

Here we get 2 open projects since one has blank end and the others end is in the future.

If we change the filter context only the campaign with blank end is open:
I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/