Measures from different fields based on Current Month

[Anonymous]

H i All,

I am having two different tables having Site,Year Month & Actual from one table and Site ,Year Month&Forecast value from other table.

Here my scenario is based on Current month I have to show Actual & Forecast.

    The bar chart based on Year Month dimension need to plot the values of Forecast from May 2017 to Dec 2017 (As we are in May 2017) and Actual should show only from Jan2017 to Apr 2017. I have attached sample file. Let me know if I am not clear.

-Jai

[maxgro · Accepted solution]

CHART

SCRIPT

T:

LOAD Month,

    Site,

    Actual,

    -1*(Month < MonthStart(Floor(Today()))) as FlagActual

FROM

[Actuals&Forecast.xlsx]

(ooxml, embedded labels, table is Actuals);

join (T)

LOAD Site,

    Forecast,

    Month,

    -1*(Month >= MonthStart(Floor(Today()))) as FlagForecast

FROM

[Actuals&Forecast.xlsx]

(ooxml, embedded labels, table is ForeCast);

[#3]

Use Dimension- Month

Expression-if(Num(Date#(Month))<=42826,Sum(Actual),Sum(Forecast))