cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
snawalkishore Occasional Visitor
Occasional Visitor

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

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).

 

Actualdata.PNGActual Data every month will be updatedForecast_data.PNGForecast Data every month will be reduced month by month till it reaches P12Actual&Forecast.PNGrequired 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

 

 

PowerBI_output2.PNGPowerBI_output1.PNG

 

 

 

 

 

 

URL to download PBIX and xlxs

 

 

Cheers,
Nawal

1 ACCEPTED SOLUTION

Accepted Solutions
v-cherch-msft Super Contributor
Super Contributor

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

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

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
mwegener Member
Member

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

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 answer you question, please mark my post as solution, this will also help others.

v-cherch-msft Super Contributor
Super Contributor

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

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

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Users Online
Currently online: 6 members 1,050 guests
Please welcome our newest community members: