Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

CONDITIONAL DATES

Hi,

 

I'm developing a daily report in Power BI, but if the user select a date where there´s no data, Power BI should select data from the last date with information:

 

CASE 1 - The user selected a date with data: Power BI should list the information for the selected date.

CASE 2 - The user selected a date with no data: Power BI should show information from the last date for each Facility:

 

Facilities    Date              Values

---------------------------------

FAC001     2021-08-16   3,000

FAC002     2020-10-01   2,500

FAC003     2019-11-03   4,000

 

Is there a way to do this?

 

Thank you,

 

Benedito Almeida.

Need Help
  • PaulDBrown's avatar
    PaulDBrown
    4 years ago

    Try:

    Sum Values =
VAR MaxDate = MAXX(FILTER(ALL(Table[Date), Table[Date] <= SELECTEDVALUE(IndDate[Date]) && NOT(ISBLANK(SUM(Table[Values])))), Table[Date])

RETURN

CALCULATE(SUM(Table[Values]), FILTER(ALL(Table[Date]), Table[Date] = MaxDate)

8 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    4 years ago

    Anonymous Maybe something like:

    Measure =
  VAR __Date = SELECTEDVALUE('Calendar'[Date])
  VAR __Value = CALCULATE(SUM('Table'[Values]),[Date]=__Date)
  VAR __NewDate = IF(ISBLANK(__Value),MAX('Table'[Date]),__Date)
RETURN
  CALCULATE(SUM('Table'[Values]),[Date]=__NewDate)
  • PaulDBrown's avatar
    PaulDBrown
    Community Champion
    4 years ago

    You will need an independent date table for the slicer. Let's call this table IndDate

    Sum Values =
    VAR MaxDate = MAXX(FILTER(ALL(Table), Table [Date] <= SELECTEDVALUE(IndDate[Date]) && NOT(ISBLANK(SUM(Table[Values])))), Table[Date])

    RETURN

    CALCULATE(SUM(Table[Values]), FILTER(ALL(Table), Table[Date] = MaxDate)

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Good morning, Paul

      Thank you very much for your input.

      I tried to use your sugestion, but the report now returns the same value for all FACILITIES, like this:

      Facilities    Date              Values

      ---------------------------------

      FAC001     2021-08-16   90.24

      FAC002     2021-08-16   90.24

      FAC003     2021-08-16   90.24

      What I really need is that for each FACILITY the report returns its last date and value.

      Benedito Almeida.

      • PaulDBrown's avatar
        PaulDBrown
        Community Champion
        4 years ago

        Try:

        Sum Values =
VAR MaxDate = MAXX(FILTER(ALL(Table[Date), Table[Date] <= SELECTEDVALUE(IndDate[Date]) && NOT(ISBLANK(SUM(Table[Values])))), Table[Date])

RETURN

CALCULATE(SUM(Table[Values]), FILTER(ALL(Table[Date]), Table[Date] = MaxDate)
  • v-zhangti's avatar
    v-zhangti
    Community Support
    4 years ago

    Hi, Anonymous 

     

    I simulated some optional dates, hoping to achieve your expected results.

    1. Create a new column in the Date table.

     

    New date = 
IF (
    [Selectable date]
        = CALCULATE (
            MAX ( 'Table'[Date ] ),
            FILTER ( 'Table', [Date ] = EARLIER ( 'Date'[Selectable date] ) )
        ),
    [Selectable date],
    CALCULATE (
        MAX ( 'Table'[Date ] ),
        FILTER ( 'Table', [Date ] < EARLIER ( 'Date'[Selectable date] ) )
    )
)

    New value = 
CALCULATE (
    MAX ( 'Table'[Values] ),
    FILTER ( 'Table', [Date] = EARLIER ( 'Date'[New date] ) )
)

     

     

        2. The new Date table needs to establish a relationship with the original table.

     

      3. Create a new date slicer in the view, and the final result is shown in the figure.

     

    If the method I provided above can't solve your problem, what's your expected result? Could you please provide more details for it?

     

    Best Regards,

    Community Support Team _Charlotte

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hello, everyone

      I would like to thank you all for your help.

      I've decided to create a stored procedure to select the data I need for the report.

      The SP is working corretly.

      My question now is: can I pass the report slicer (it's a date) as a parameter to the stored procedure?

      This would solve my problem completely.

      Thank you all.

      Benedito_1967.

      • v-zhangti's avatar
        v-zhangti
        Community Support
        4 years ago

        Hi, Anonymous 

         

        Create a new calendar table.

         

        Date = CALENDAR(MIN('Table'[Date]),MAX('Table'[Date]))

         

         

        New measure takes the date selected in the slicer.

         

        New Date = MAX('Date'[Date])

        Output date = 
IF (
    [New Date] = MAX ( 'Table'[Date] ),
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER ( ALL ( 'Table' ), [Date] = MAX ( 'Table'[Date] ) )
    ),
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER ( ALL ( 'Table' ), [Date] < MAX ( 'Date'[Date] ) )
    )
)

        New Facilities =
CALCULATE (
    MAX ( 'Table'[Facilities] ),
    FILTER ( ALL ( 'Table' ), [Output date] = MAX ( 'Table'[Date] ) )
)

        New value = 
CALCULATE (
    MAX ( 'Table'[Values] ),
    FILTER ( ALL('Table'), [Date] =MAX('Table'[Date] ) )
)

         

         

        Is this the case when the date slicer you mentioned is selected as a parameter? If you have any other questions, please reply.

         

        Best Regards,

        Community Support Team _Charlotte

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.