none
countifs in DAX Filter by 3 dates RRS feed

  • Question

  • HI All'

    In excel I can count projects with filter of start date,  projects  before the end date and date of current cell or selected cell value as reference.

    How can I do this in Dax?  Formula : "=COUNTIFS($B$2:$B$16,"<"&E2,C2:C16,E2)"

    please see data for reference 

    A B C D E F G
    proj 1 5-May-19 2-Aug-19 2-Aug-19 9
    proj 2 6-May-19 3-Aug-19
    proj 3 7-May-19 4-Aug-19
    proj 4 8-May-19 8-May-19
    proj 5 9-May-19 2-Aug-19
    proj 6 2-Jun-19 2-Aug-19
    proj 7 3-Jun-19 2-Aug-19
    proj 8 4-Jun-19 2-Aug-19
    proj 9 5-Jun-19 2-Aug-19
    proj 10 9-Jul-19 2-Aug-19
    proj 11 10-Jul-19 2-Aug-19
    proj 12 11-Jul-19 2-Aug-19
    proj 13 2-Aug-19 2-Aug-19
    proj 14 2-Aug-19 2-Aug-19
    proj 15 2-Aug-19 3-Aug-19

    • Edited by reggiene Friday, August 23, 2019 2:18 PM clarified criteria
    Friday, August 23, 2019 10:40 AM

All replies

  • Excel 365 Pro Plus with PowerPivot and Power Query (aka Get & Transform)
    PP equivalent of CountIfs()
    Two ways: Calculated Column, Measure.
    http://www.mediafire.com/file/ok6iy9t02mg6pcl/08_26_19b.xlsx/file
    http://www.mediafire.com/file/1crn6b531tvqso4/08_26_19b.pdf/file

    Tuesday, August 27, 2019 5:10 PM
  • Hi Herbert,

    thanks for reply on this. but that is not giving me the result that i need. maybe i didn't explain my self clearly. Please allow me to clarify.

    if Date_B current row value is Aug 2, 2019, then filter the Date_A before Aug 2, then filter Date_B for Aug 2, 2019.

    please see cells attached that i replicate in excel countifs. really appreciate the help.

     

     

    Criteria Date on Current Row

    Result

    Formula in Countifs

     

     

    Date_A

    Date_B

    2-Aug-19

    15

    "=COUNTIFS(AT3:AT50,"<"&AT3,AU3:AU50,AT1)"

    31-Jul-19

    2-Aug-19

     

     

     

    1-Aug-19

    2-Aug-19

     

     

     

    1-Aug-19

    2-Aug-19

     

     

     

    1-Aug-19

    2-Aug-19

     

     

     

    1-Aug-19

    2-Aug-19

     

     

     

    1-Aug-19

    2-Aug-19

     

     

     

    1-Aug-19

    2-Aug-19

     

     

     

    1-Aug-19

    2-Aug-19

     

     

     

    1-Aug-19

    2-Aug-19

     

     

     

    1-Aug-19

    2-Aug-19

     

     

     

    1-Aug-19

    2-Aug-19

     

     

     

    1-Aug-19

    2-Aug-19

     

     

     

    1-Aug-19

    2-Aug-19

     

     

     

    1-Aug-19

    2-Aug-19

     

     

     

    1-Aug-19

    2-Aug-19

     

     

     




    • Edited by reggiene Wednesday, August 28, 2019 10:04 AM
    Wednesday, August 28, 2019 10:01 AM
  • Your second post obfuscates your request even more.
    Share a file with all the possible combinations of all three dates,
    the Countifs() formulas for each combination, and the desired numbers.
    My example was not designed to solve your specific data or relationship,
    but to get the same results of an arbitrary Countifs() function in PowerPivot.

    Wednesday, August 28, 2019 9:34 PM
  • Hello, sorry to have confused you. I have tried uploading file but the forum requires my account to be verified. I guess it may be a few days after my request is granted.

    if I may still try to ask for help:

     

    my data model has 2 dates columns (project start date, DATEA and status date, DATEB). I have a dates table too.

    In pivot table i would like to be able to pull out the counts of occurrence of status dates DATEA when a project start date DATEB is before the status date DATEA.

    Hence in excel formula there is a static cell where i get the criteria

     Formula: "=COUNTIFS(Table1[DateA],"<"&F1,Table1[DateB],F1)

    DATEB criteria : 08/21/1

    Result Count =6

    in DAX i would need the criteria to be the row dates of Date B

    Proj DateA DateB
    BED 08/14/19 08/08/19
    BID 08/21/19 08/28/19
    BOT 08/20/19 08/28/19
    BUB 08/05/19 08/26/19
    DAB 08/03/19 08/08/19
    DAK 08/28/19 08/03/19
    DAL 08/01/19 08/27/19
    DAS 08/28/19 08/21/19
    DEH 08/09/19 08/09/19
    DEK 08/15/19 08/19/19
    DES 08/30/19 08/13/19
    DET 08/11/19 08/19/19
    DIS 08/05/19 08/13/19
    DOK 08/18/19 08/22/19
    DOM 08/13/19 08/26/19
    DOR 08/15/19 08/03/19
    DUL 08/21/19 08/17/19
    FAF 08/14/19 08/17/19
    FEG 08/06/19 08/03/19
    FEK 08/18/19 08/22/19
    FIG 08/11/19 08/07/19
    FIL 08/19/19 08/29/19
    FOP 08/27/19 08/11/19
    GAN 08/10/19 08/28/19
    GED 08/08/19 08/05/19
    GER 08/09/19 08/09/19
    GID 08/29/19 08/21/19
    GIM 08/30/19 08/17/19
    GOS 08/08/19 08/29/19
    GOT 08/13/19 08/26/19
    GUB 08/01/19 08/08/19
    GUD 08/06/19 08/26/19
    HAG 08/24/19 08/22/19
    HIG 08/10/19 08/16/19
    HIK 08/15/19 08/02/19
    HOT 08/20/19 08/01/19
    HUB 08/01/19 08/13/19
    HUF 08/09/19 08/14/19
    HUP 08/19/19 08/14/19
    JAK 08/28/19 08/30/19
    JED 08/20/19 08/23/19
    JES 08/29/19 08/16/19
    JUD 08/20/19 08/10/19
    KAS 08/23/19 08/24/19
    KEG 08/28/19 08/30/19
    KIM 08/24/19 08/05/19
    LAT 08/28/19 08/19/19
    LEF 08/08/19 08/07/19
    LIL 08/03/19 08/18/19
    LOF 08/06/19 08/28/19
    LOK 08/02/19 08/06/19
    LUP 08/10/19 08/21/19
    MIS 08/28/19 08/04/19
    MOF 08/05/19 08/17/19
    MOR 08/07/19 08/15/19
    MUH 08/11/19 08/23/19
    NAD 08/09/19 08/12/19
    NAH 08/21/19 08/20/19
    NEK 08/10/19 08/16/19
    NEM 08/03/19 08/02/19
    NIS 08/21/19 08/30/19
    NUG 08/14/19 08/12/19
    PIG 08/21/19 08/08/19
    POF 08/29/19 08/29/19
    POK 08/23/19 08/18/19
    PON 08/05/19 08/26/19
    PUG 08/13/19 08/16/19
    REH 08/07/19 08/14/19
    ROB 08/07/19 08/15/19
    RUM 08/28/19 08/18/19
    RUS 08/06/19 08/18/19
    SAD 08/09/19 08/16/19
    SAF 08/25/19 08/05/19
    SIL 08/02/19 08/13/19
    SIS 08/26/19 08/24/19
    SUF 08/03/19 08/05/19
    TIM 08/02/19 08/07/19
    TIR 08/17/19 08/26/19
    TUG 08/11/19 08/28/19
    VAT 08/08/19 08/24/19
    VEG 08/13/19 08/28/19
    VEP 08/09/19 08/10/19
    VES 08/05/19 08/23/19
    VET 08/20/19 08/09/19
    VID 08/03/19 08/16/19
    VIF 08/07/19 08/15/19
    VIH 08/02/19 08/10/19
    VIM 08/22/19 08/20/19
    VIP 08/18/19 08/07/19
    VOT 08/19/19 08/10/19
    WAS 08/20/19 08/27/19
    WEM 08/12/19 08/24/19  


    appreciate the help.

     
    Thursday, August 29, 2019 6:19 PM
    1. I asked for your data, not a copy of mine.
    2. Your Countifs() formula alternates between Table refs and Cell refs.
      COUNTIFS(Table1[DateA],"<"&Table1[@DataC],Table1[DateB],Table1[@DataC])
      would be more self-consistent.
    3. Once you invoke PowerPivot, all cell references like F1 (or $F$1), cease to exist.
      TableX[DataC] would be a valid reference in the right context.
    4. My PivotTables are for a more general case, and can be reduced to a single reference.
    5. "Building Data Models with PowerPivot" by Ferrari and Russo is a good primer.
    6. No Mas.
    Thursday, August 29, 2019 11:19 PM