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 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

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/

1 Reply

  • 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/