Show data in Matrix visual as Actuals for months completed and rest as forecast for future months

[snawalkishore]

Hello Experts,

Please find my requirement. 

I will receive actual every month (Screenshot1), however I must consider only till last month (i.e Month(now)-1) which should be dynamic. Also I will receive forecast (as in Screenshot2)

Requirement is in last screenshot. Till P01 to P04 Actual Data(40) and from P05 to P12 forecast Data(60) 
and finally total of Actual and forecast(100).

Actual Data every month will be updatedForecast Data every month will be reduced month by month till it reaches P12required output in Power BI

I have wrote DAX Measures, however its not showing correct output.
I'm not sure where is my logic need to be changed, As its calculating both Actual and forecast(102) or calculating only Forecast(60). Its not giving 100

URL to download PBIX and xlxs

Cheers,
Nawal

[v-cherch-msft · Accepted solution]

Hi @snawalkishore

You may add a month number column for actual table as below. Then you may get the value with a measure. Here is the sample file for your reference.

MonthNo = MONTH(DATEVALUE(Actual[MonthName]&"/1"))

Measure =
CALCULATE (
    SUM ( Actual[Hours] ),
    FILTER (
        Actual,
        Actual[MonthNo]
            <= MONTH ( NOW () ) - 1
            && Actual[Type] = "Actual"
            || Actual[Type] = "Forecast"
    )
)

Regards,

Cherie

[mwegener]

Hi,

maybe you should extract the numeric month value with Power Query and filter on it.

Sample:

let
    Source = Excel.Workbook(File.Contents("C:\xxx\Test.xlsx"), null, true),
    Actual_Sheet = Source{[Item="Actual",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Actual_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"MonthName", type text}, {"Month", type text}, {"Hours", type number}, {"Type", type text}}),
    #"Inserted Last Characters" = Table.AddColumn(#"Changed Type", "Last Characters", each Text.End([Month], 2), type text),
    #"Changed Type1" = Table.TransformColumnTypes(#"Inserted Last Characters",{{"Last Characters", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Last Characters", "Month Digit"}}),
    #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each [Month Digit] < Date.Month(DateTime.LocalNow()) -1)
in
    #"Filtered Rows"

If I answered your question, please mark my post as solution, this will also help others.