Date Range

[Anonymous]

Hi,

I have a formula but I am looking at changing it do the date range is specifically between two dates?

2017 YTD  = (IF (CALCULATE (SUM('Data Dump - Spend'[Credit (incl VAT)])) = BLANK(),0,

CALCULATE (SUM('Data Dump - Spend'[Credit (incl VAT)]),FILTER (ALL('Data Dump - Trade Spend'[Invoice Date] ),'Data Dump - Spend'[Invoice Date] <= DATE ( 2017, 6, 1 )))))

How could I change it to be between 01.04.2017 and 01.06.2017?

Kind regards

Dom

[Anonymous · Accepted solution]

@Anonymous,

You can use this:

2017 YTD =
 (
    IF (
        CALCULATE ( SUM ( 'Data Dump - Spend'[Credit (incl VAT)] ) ) = BLANK (),
        0,
        CALCULATE (
            SUM ( 'Data Dump - Spend'[Credit (incl VAT)] ),
            FILTER (
                ALL ( 'Data Dump - Trade Spend'[Invoice Date] ),
                'Data Dump - Spend'[Invoice Date] <= DATE ( 2017, 6, 1 )
                    && 'Data Dump - Spend'[Invoice Date] >= DATE ( 2017, 4, 1 )
            )
        )
    )
)

[Anonymous · Accepted solution]

@Anonymous,

You can use this:

2017 YTD =
 (
    IF (
        CALCULATE ( SUM ( 'Data Dump - Spend'[Credit (incl VAT)] ) ) = BLANK (),
        0,
        CALCULATE (
            SUM ( 'Data Dump - Spend'[Credit (incl VAT)] ),
            FILTER (
                ALL ( 'Data Dump - Trade Spend'[Invoice Date] ),
                'Data Dump - Spend'[Invoice Date] <= DATE ( 2017, 6, 1 )
                    && 'Data Dump - Spend'[Invoice Date] >= DATE ( 2017, 4, 1 )
            )
        )
    )
)

[Anonymous]

Thanks, this works really well.

[Anonymous]

@Anonymous Although the empty value now shows up as blank instead of £0?

[Anonymous · Accepted solution]

@Anonymous,

Try this instead: 

2017 YTD =
IF (
    CALCULATE ( SUM ( 'Data Dump - Spend'[Credit (incl VAT)] ) ) = BLANK (),
    0,
    CALCULATE (
        SUM ( 'Data Dump - Spend'[Credit (incl VAT)] ),
        FILTER (
            ALL ( 'Data Dump - Trade Spend'[Invoice Date] ),
            'Data Dump - Spend'[Invoice Date] <= DATE ( 2017, 6, 1 )
                && 'Data Dump - Spend'[Invoice Date] >= DATE ( 2017, 4, 1 )
        )
    )
        + 0
)

[Anonymous]

How strange! Thanks Nick.