Forum Discussion

mohammadrafic's avatar
mohammadrafic
Helper I
5 years ago
Solved

Future date with latest data

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

 

 

Need Help
  • v-xiaotang's avatar
    v-xiaotang
    5 years ago

    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.

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    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.

  • mohammadrafic's avatar
    mohammadrafic
    Helper I
    5 years ago

    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.

     

     

  • v-xiaotang's avatar
    v-xiaotang
    Community Support
    5 years ago

    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's avatar
      mohammadrafic
      Helper I
      5 years ago

      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