Future date with latest data

[mohammadrafic]

hi,

i have card visual and date between slicer. when i select future date it should show the last day data in card visual.

my table i have date and data columns but for the future date i dont have data because it is forecast data.  please solve this issue. thanks

[v-xiaotang · Accepted solution]

Hi @mohammadrafic 

Based on your sample data, I create a sample. See sample file attached bellow.

-

coz you want put values into card, in card there's no row context so you need to provide context to filter manually in measure,

Measure = 
var _currentMonth=CALCULATE(MAX('Table'[StartDate]),ALL('Table'))
var _selMonth=SELECTEDVALUE(dateSlicer[StartDate])
var _lastMonth=EDATE(_currentMonth,-1)
var _YTD1=CALCULATE(MAX('Table'[Forecast_YTD_AVG]),FILTER(ALL('Table'),'Table'[StartDate]=_lastMonth&&'Table'[TITLE]="Light-Total"))
var _YTD2=CALCULATE(MAX('Table'[Forecast_YTD_AVG]),FILTER(ALL('Table'),'Table'[StartDate]=_lastMonth&&'Table'[TITLE]="PZ-Total"))
var _YTD3=CALCULATE(MAX('Table'[Forecast_YTD_AVG]),FILTER(ALL('Table'),'Table'[StartDate]=_lastMonth&&'Table'[TITLE]="Ratawi-Total"))
var _YTD4=CALCULATE(MAX('Table'[Forecast_YTD_AVG]),FILTER(ALL('Table'),'Table'[StartDate]=_lastMonth&&'Table'[TITLE]="SUGAMT-Total"))
return
"Light-Total: "&_YTD1&"   PZ-Total: "&_YTD2&"   Ratawi-Total: "&_YTD3&"   SUGAMT-Total: "&_YTD4

result

Best Regards,

Community Support Team _Tang

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

[mohammadrafic]

Hi v-xiaotang. Great it works your idea. but i fixed through oracle sql fill the data till end of the year bcz i need complete year. thank you.

LAST_VALUE (FORECAST_YTD_AVG)
IGNORE NULLS
OVER (PARTITION BY table_name
ORDER BY table_name, start_date
ROWS BETWEEN UNBOUNDED PRECEDING AND 0 PRECEDING)
FORECAST_YTD_AVG

[mohammadrafic]

in july i dont have data which is highlighted in yellow but when i select july in slicer i wanted to see. it should use the last value which is june data. and i am not using date table that is not required here.

[amitchandak]

@mohammadrafic , Not very clear. Can you explain with an example? Make sure you are using the date table and the Date table has that future date.