calculate diff between two dates but in a specific month

[Anonymous]

looking to calculate day diff, but in a specific month. so in the example below, a new column to show days in Jan2012, first record should show 6 and others 0.

basically looking for a visual to show the total number of DAYSdiff in a specific month, there might be an easier way than above.

any help is appreciated!

 

[v-lili6-msft]

hi, @Anonymous 

For your case, you could try this way as below:

Step1:

Create a separate date table for select specific month.

Date =
ADDCOLUMNS (
    CALENDARAUTO (),
    "YearMonth", YEAR ( [Date] ) * 100
        + MONTH ( [Date] )
)

Step2:

Use this logic to create a measure as below:

DAYSdiff = 
IF (
    SELECTEDVALUE ( 'Table'[DATE1] ) <= MAX ( 'Date'[Date] )
        && SELECTEDVALUE ( 'Table'[DATE1] ) <= MIN ( 'Date'[Date] ),
    DATEDIFF ( MIN ( 'Date'[Date] ), MAX ( 'Date'[Date] ) + 1, DAY ),
    IF (
        SELECTEDVALUE ( 'Table'[DATE1] ) <= MAX ( 'Date'[Date] ),
        DATEDIFF ( SELECTEDVALUE ( 'Table'[DATE1] ), MAX ( 'Date'[Date] ) + 1, DAY ),
        0
    )
)

Result:

here is my sample pbix, please try it.

Best Regards,

Lin

[Anonymous]

Although it is informative, it is not the solution.

in the scenario below, only record 3 should have 30 days and all other record should be 0.

[v-lili6-msft · Accepted solution]

hi, @Anonymous 

If you try to improve the formula as below:

Measure 2 = 
IF (
    SELECTEDVALUE ( 'Table'[DATE1] ) <= MAX ( 'Date'[Date] )
        && SELECTEDVALUE ( 'Table'[DATE1] ) >= MIN ( 'Date'[Date] ),
 
    DATEDIFF ( SELECTEDVALUE ( 'Table'[DATE1] ), MAX ( 'Date'[Date] ) + 1, DAY ),
    IF (
        SELECTEDVALUE ( 'Table'[DATE1] ) <= MIN ( 'Date'[Date] )
            && SELECTEDVALUE ( 'Table'[DATE2] ) >= MAX ( 'Date'[Date] ),

        DATEDIFF ( MIN ( 'Date'[Date] ), MAX ( 'Date'[Date] ) + 1, DAY ),
        IF (
            SELECTEDVALUE ( 'Table'[DATE1] ) <= MIN ( 'Date'[Date] )
                && SELECTEDVALUE ( 'Table'[DATE2] ) <= MAX ( 'Date'[Date] )&& SELECTEDVALUE ( 'Table'[DATE2] )>=MIN('Date'[Date]),

            DATEDIFF ( MIN ( 'Date'[Date] ), SELECTEDVALUE ( 'Table'[DATE2] ) + 1, DAY ),
            IF (
                SELECTEDVALUE ( 'Table'[DATE1] ) >= MIN ( 'Date'[Date] )
                    && SELECTEDVALUE ( 'Table'[DATE2] ) <= MAX ( 'Date'[Date] ),
                DATEDIFF ( SELECTEDVALUE ( 'Table'[DATE1] ), MAX ( 'Date'[Date] ) + 1, DAY ),
                0
            )
        )
    )
)

Best Regards,

Lin

[amitchandak]

Created as column

Diff from date1 = 
VAR seldate = DATE(2013,05,31)
Return 
if(Sales[Order_Date] < seldate, DATEDIFF(Sales[Order_Date],seldate,DAY),0)

[amitchandak]

In case you want a measure dependent on time/calendar

Diff from date3 = 
VAR seldate = max('OrderTime'[Order Date])
Return 
CALCULATE(sumx(Sales, if(Sales[Order_Date] < seldate, DATEDIFF(Sales[Order_Date],seldate,DAY),0)))