Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

DAX - Count Days Between two date fields on a Date Axis

Hello,   I'm trying to use DAX to count every day between two date fields for a large dataset and create a stacked column chart broken out by Program.  You can do this in excel by means of the COUN...
Need Help
  • bolfri's avatar
    bolfri
    3 years ago

    Hi,

     

    hope this will help you. Dynamic measure based on your calendar.

    Count Correct Rows =
    var date_ref = SELECTEDVALUE(dim_calendar[Date])
    return
        COUNTX(
            FILTER('Sample';AND('Sample'[Status Start Date] <= date_ref;'Sample'[Status End Date]>=date_ref));
            COUNTROWS('Sample')
        )
     
    You can use Program column as you want. No relationship needed.

     

bolfri's avatar
bolfri
Icon for Solution Sage rankSolution Sage
3 years ago

Hi,

 

hope this will help you. Dynamic measure based on your calendar.

Count Correct Rows =
var date_ref = SELECTEDVALUE(dim_calendar[Date])
return
    COUNTX(
        FILTER('Sample';AND('Sample'[Status Start Date] <= date_ref;'Sample'[Status End Date]>=date_ref));
        COUNTROWS('Sample')
    )
 
You can use Program column as you want. No relationship needed.

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi bolfri ,

     

    This Solution does exactly as expected.  Thank you!

     

    One follow-up question.  I'd like to show date hierarchy on my axis, with date rolling up to Month & Quarter.  PBI will not accept the Month or Quarter axis - visual shows blank when I try that.  Is there a way to rewrite this to account for those?  Can they be included as variables?

     

    Thanks again!

    • bolfri's avatar
      bolfri
      Icon for Solution Sage rankSolution Sage
      3 years ago

      Sure. I needed to change it a little, but this i a result:

      Count Correct Rows - Aggregatable =
      var min_date = FIRSTDATE(dim_calendar[Date])
      var max_date = LASTDATE(dim_calendar[Date])

      return
          COUNTX(
              FILTER('Sample';
                  AND(
                      OR(
                          OR(
                              //Scenario 1
                              AND('Sample'[Status Start Date]<=min_date;'Sample'[Status End Date]>=max_date);
                              //Scenario 2
                              AND('Sample'[Status Start Date]>=min_date;'Sample'[Status End Date]<=max_date)
                          );
                          OR(
                              //Scenario 3
                              AND('Sample'[Status Start Date]<=min_date;AND('Sample'[Status End Date]>=min_date;'Sample'[Status End Date]<=max_date));
                              //Scenario 4
                              AND(AND('Sample'[Status Start Date]>=min_date;'Sample'[Status Start Date]<=max_date);'Sample'[Status End Date]>=max_date)
                          )
                      );
                  //Exclude all records with wrong data, when start is after end
                  'Sample'[Status Start Date] <= 'Sample'[Status End Date]
                  )
              );
              COUNTROWS('Sample')
          )
       
      Graphic representation on each scenario:

       

      Result:
      Count Correct Rows - Aggregatable - new one

      Count Correct Rows - old one, that works only for day representation