Forum Discussion

bendejnp's avatar
bendejnp
Icon for Helper I rankHelper I
4 years ago
Solved

Date Filter with daily update

Hello together,

 

I have a problem and I am trying to solve it since a couple of days. 

 

I have created report that is published PBI services and it is updated automatically every morning containing sales data from previous day. It shows MTD and YTD figures and compares them to previous year. I do this with time intelligence functions.

 

What I would like to have:

1. A date filter that jumps automatically alway to the date of the latest figures (previous day) 

2. Also I want to give the user the opportunity to select days in the past

 

Currently I am using a date filter with "relative date" where "today" is selected. The problem here is that it takes the figures from yesterday and compares it figures from previous, but with one day more. Due to that the comparison is not correct. Furthermore the user does not have the chance to select a date in the past.

 

Do you know a user friendly solution for this problem?

 

Best regards

Jan

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi bendejnp ,

     

    Please create a measure like below and add it to visual filter set value = 1.

    flag =
SWITCH (
    TRUE (),
    SELECTEDVALUE ( slicer[date] ) = "today", IF ( SELECTEDVALUE ( 'fact'[date] ) = TODAY (), 1, 0 ),
    SELECTEDVALUE ( slicer[date] ) = "yesterday",
        IF ( SELECTEDVALUE ( 'fact'[date] ) = TODAY () - 1, 1, 0 ),
    IF (
        FORMAT ( SELECTEDVALUE ( 'fact'[date] ), "YYYYMMDD" ) IN VALUES ( slicer[date] ),
        1,
        0
    )
)

     

    Best Regards,

    Jay

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi bendejnp ,

     

    Please create a measure like below and add it to visual filter set value = 1.

    flag =
SWITCH (
    TRUE (),
    SELECTEDVALUE ( slicer[date] ) = "today", IF ( SELECTEDVALUE ( 'fact'[date] ) = TODAY (), 1, 0 ),
    SELECTEDVALUE ( slicer[date] ) = "yesterday",
        IF ( SELECTEDVALUE ( 'fact'[date] ) = TODAY () - 1, 1, 0 ),
    IF (
        FORMAT ( SELECTEDVALUE ( 'fact'[date] ), "YYYYMMDD" ) IN VALUES ( slicer[date] ),
        1,
        0
    )
)

     

    Best Regards,

    Jay

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    bendejnp , create a new column like below in date  table and default today in the slicer

     

    Date Type = SWITCH(TRUE(),'Date'[Date]=TODAY(),"Today"
    ,'Date'[Date]=TODAY()-1,"Yesterday"
    ,'Date'[Date]&"")

     

     

    measures

    MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))

     

    YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))

  • bendejnp's avatar
    bendejnp
    Icon for Helper I rankHelper I
    4 years ago

    Thank you, that works...almost... 🙂

     

    The problem is that the new column cannot be converted to "date typ" because it includes "yesterday" and "today" as values. If the dateyp is not possible, the functions DATESMTD and DATESYTD do also not work.