Forum Discussion

AlAlawiAlawi's avatar
AlAlawiAlawi
Helper I
8 years ago
Solved

Change Values after Comparing it to the Filtered Date

Hi Guys,

 

Long Story Short in the Screen Shot Below

What_should_I_do

I am trying to get what deals were in progress on that previous day, Ideally I would have a date range but I thought to simplify the issue lets choose 1 Day as a filtered in the Pivot Table above.

 

I need a Measure That Checks the Date Filtered Value then Calculates if the Called In Date >= Filtered Date && Invoiced Date <= Filtered Date.

 

What is Filtered Date (Slicer Values) ? What should I put in the Slicer as a field ? Do I need to Create a separate Table for this ?

BI

Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    Hi AlAlawiAlawi,

     

    You may need to create an extra date table similar to:

    Dimdate =
CALENDAR ( DATE ( 2018, 1, 1 ), DATE ( 2018, 12, 31 ) )

    Add 'Dimdate'[Date] column into slicer.

     

    Then, you can create such a measure:

    CountID =
CALCULATE (
    COUNT ( table[ID] ),
    FILTER (
        table,
        table[Called In Date] >= MAX ( 'Dimdate'[Date] )
            && table[Invoiced Date] <= MAX ( 'Dimdate'[Date] )
    )
)

    Best regards,

    Yuliana Gu

1 Reply

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    8 years ago

    Hi AlAlawiAlawi,

     

    You may need to create an extra date table similar to:

    Dimdate =
CALENDAR ( DATE ( 2018, 1, 1 ), DATE ( 2018, 12, 31 ) )

    Add 'Dimdate'[Date] column into slicer.

     

    Then, you can create such a measure:

    CountID =
CALCULATE (
    COUNT ( table[ID] ),
    FILTER (
        table,
        table[Called In Date] >= MAX ( 'Dimdate'[Date] )
            && table[Invoiced Date] <= MAX ( 'Dimdate'[Date] )
    )
)

    Best regards,

    Yuliana Gu