Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Date Range

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,...
Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    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's avatar
    Anonymous
    8 years ago

    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's avatar
Anonymous
Not applicable
8 years ago

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's avatar
    Anonymous
    Not applicable
    8 years ago

    Thanks, this works really well.

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        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
)