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...
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)
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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Hello,

        I would like to thank you all for your help.

        You really saved me hours of work.

        Benedito_1967.