Forum Discussion

aggiebrown's avatar
aggiebrown
Icon for Helper III rankHelper III
2 years ago

Optimising Forecast Measures in Matrix Table: Filter Table to Future Dates for Some Measures only

Hi all,

 

I am having issues when trying to optimise what currently is an excel spreadsheet in Power BI. 

 

I have a basic Matrix Table that shows a few KPIs with current data month to date, and forecasting for the end of the month based on rolling 4 week / 1 week averages. I am using date table in a few VAR measures To get Forecasted Metrics and Scheduled in Future metric (from today onwards only, but when Matrix Totals rows it Totals it for the full month and not only from Today)  

 

This is the expected view I want to get to Power BI:

And this is currently what I am getting in Power BI highlighting in red what is not calculating correctly:

 

All measures are based on this fact_table which is connected to date table via "datekey" column:

 

 

I have a Power BI version of this, with measures I written down.

Power BI Desktop File

 

Any ideas on how to solve the 3 highlighted measures?

 

Need Help
Show and Tell
Tutorial Requests

1 Reply

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    2 years ago

    aggiebrown , for Meetings Scheduled (only future) try like

    # Meetings Scheduled (only future) = 


VAR __Result = 
                         CALCULATE([# Meetings Scheduled], FILTER(dim_date, dim_date[date]>=TODAY()))

RETURN __Result