Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Count Difference between dates broken out by month

Is there a way to calculate the difference between two dates but break them out by months?   My table example is: ID Date Start Date End Duration 123 12/11/2018 16/11/2018 5 123 02...
COUNT
DATEDIFF
Difference
Month
Need Help
  • wdx223_Daniel's avatar
    wdx223_Daniel
    5 years ago

    Anonymous  firstly, you need create a date table, which have no relationship with your fact table. then try this code

    DaysCount :=
SUMX (
    Table1,
    VAR sd = Table1[Date Start]
    VAR ed = Table1[Date End]
    RETURN
        CALCULATE (
            COUNT ( 'Calendar'[Date] ),
            KEEPFILTERS ( DATESBETWEEN ( 'Calendar'[Date], sd, ed ) )
        )
)

     

     

wdx223_Daniel's avatar
wdx223_Daniel
Icon for Community Champion rankCommunity Champion
5 years ago

Anonymous  firstly, you need create a date table, which have no relationship with your fact table. then try this code

DaysCount :=
SUMX (
    Table1,
    VAR sd = Table1[Date Start]
    VAR ed = Table1[Date End]
    RETURN
        CALCULATE (
            COUNT ( 'Calendar'[Date] ),
            KEEPFILTERS ( DATESBETWEEN ( 'Calendar'[Date], sd, ed ) )
        )
)