Forum Discussion

chq's avatar
chq
Icon for Helper II rankHelper II
6 years ago
Solved

Refunds based on Date Slicer

I have data that currently looks like the following...

IDTicket DateRefund DateQTY
112/01/201912/02/2019-1
212/07/2019null1
312/09/2019null1
412/10/201912/22/2019-1
512/11/201912/12/2019-1


Is there a way to create a measure or column on a page where the QTY is a variable based on a Ticket Date Slicer? Meaning that if the Ticket Date is greater than or equal to the Refund date the QTY would equal -1, but if the Ticket Date in the slicer was less than the Refund date the QTY would equal 1?

Thanks!

Need Help
  • v-gizhi-msft's avatar
    v-gizhi-msft
    6 years ago

    Hi,

     

    Please try this:

    Qty = 
VAR _max =
    MAX ( SlicerTable[Ticket Date] )
VAR _min =
    MIN ( SlicerTable[Ticket Date] )
RETURN
    SUMX (
        DISTINCT ( 'Table'[ID] ),
        CALCULATE (
            IF (
                MAX ( 'Table'[Refund Date] ) <= _max
                    && MAX ( 'Table'[Refund Date] ) >= _min,
                1,
                -1
            )
        )
    )

    The result shows:

    See my attached pbix file.

     

    Best Regards,

    Giotto

7 Replies

  • BA_Pete's avatar
    BA_Pete
    Icon for Super User rankSuper User
    6 years ago

    Hi chq 

     

    Sorry if I'm oversimpliying your requirement here, but a measure to create your desired output would be something like this:

    _qtyMeasure = 
VAR tDate = MAX(aTable[ticketDate])
VAR rDate = MAX(aTable[refundDate])
RETURN
IF(
    tDate >= rDate,
    1,
    -1
)

     

    This gives me the following output:

     

    Pete

    • chq's avatar
      chq
      Icon for Helper II rankHelper II
      6 years ago

      If I am using the date a SLICER and move the date back to say 10/01/2019, would it show all of those values as 1 for the new column?

      • BA_Pete's avatar
        BA_Pete
        Icon for Super User rankSuper User
        6 years ago

        Ok, I think I see what you mean.

        This requires a disconnected/unrelated date table - I've called it 'disconCal' in the below measure:

        _qtyMeasure = 
VAR tDate = MAX(disconCal[Date])
VAR rDate = MAX(aTable[refundDate])
RETURN
IF(
    ISBLANK(rDate),
    1,
    IF(
        tDate >= rDate,
        -1,
        1
    )
)

         

        This gives the following outputs for a date selected before/during/after the example period:

        Selected date AFTER example periodSelected date BEFORE example periodSelected date DURING example period

         

        The key here is that the date table you use for your slicer must not be related to your fact table.

         

        Pete