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.

     

Bifinity_75's avatar
Bifinity_75
Solution Sage
3 years ago

Hi Anonymous , how many programas have you got?. 
if you have few programs try this:

- Create a diferent calculate column for every program:

 

COUNTIFS_A = CALCULATE(COUNTROWS('Program Status Table'), 
FILTER(ALL('Program Status Table'),'Program Status Table'[Status Start Date] <='Date Table'[Date] && 'Program Status Table'[Status End Date]>='Date Table'[Date]),'Program Status Table'[Program]="A")
COUNTIFS_B = CALCULATE(COUNTROWS('Program Status Table'),
FILTER(ALL('Program Status Table'),'Program Status Table'[Status Start Date] <='Date Table'[Date] && 'Program Status Table'[Status End Date]>='Date Table'[Date]),'Program Status Table'[Program]="B")
COUNTIFS_C = CALCULATE(COUNTROWS('Program Status Table'),
FILTER(ALL('Program Status Table'),'Program Status Table'[Status Start Date] <='Date Table'[Date] && 'Program Status Table'[Status End Date]>='Date Table'[Date]),'Program Status Table'[Program]="C")

 

So you can use the programs as a legend for your chart

 

The result:

 

Best Regards