Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Calculating Active

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...
DAX help needed
  • ValtteriN's avatar
    ValtteriN
    2 years ago

    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/
ValtteriN's avatar
ValtteriN
Community Champion
2 years ago

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/