Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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).
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
Cheers,
Nawal
Solved! Go to Solution.
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
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
118 | |
107 | |
70 | |
70 | |
43 |
User | Count |
---|---|
148 | |
105 | |
104 | |
89 | |
65 |