CONDITIONAL DATES

[Anonymous]

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.

[v-zhangti]

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]

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]

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]

Hello,

I would like to thank you all for your help.

You really saved me hours of work.

Benedito_1967.

[PaulDBrown]

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]

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 · Accepted solution]

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)

[Greg_Deckler]

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