Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
I have dataset ('SAP Line Items FY') with 2 measures [Scaled Actual Amount ($AOPfx)] and [Scaled Plan Amount ($AOPfx)]. This dataset has a relationship with another table containing the names of the 12 months ('Months'). The join is:
'Months'[Monthnum] 1:Many 'SAP Line Items FY'[Fiscal Period]
I have a slicer for 'Months'[Month] so the report user can select the current month (using the name rather than fiscal period number). Then what I want to do is create a new measure, for the months up to the one selected it should include the Actuals [Scaled Actual Amount ($AOPfx)] and for future months it should use [Scaled Plan Amount ($AOPfx)].
I have come up with
Solved! Go to Solution.
Hi @andybrace ,
Because you have a relationship between the two tables,that's why the result is what you are after.
Create a slicer table as below:
Slicer table = SELECTCOLUMNS('Months',"Month",'Months'[Month],"Monthnum",'Months'[Monthnum])
Then create a measure :
Measure 3 =
var _tab=SUMMARIZE('SAP Line Items FY','SAP Line Items FY'[Fiscal Period],"value",IF(MAX('SAP Line Items FY'[Fiscal Period])<='SAP Line Items FY'[Measure 2],[Scaled Actual Amount ($AOPfx) 1],[Scaled Plan Amount ($AOPfx)1]))
Return
SUMX(_tab,[value])
And you will see:
For the related .pbix file,pls see attached.
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
Hi @andybrace ,
Could you pls provide some dummy data with expected output for test?
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
Thanks for the reply @v-kelly-msft .
Please see example on Google Drive:https://drive.google.com/file/d/1-X41c4UlLibKsZ5ppSNBv0zgrdqJVIOr/view?usp=sharing
The Matrix at the bottom is showing the output I want. The first 2 months are showing the "Actual" data, the rest of the months are showing "Plan" data. However, to get this I have hard coded
Hi @andybrace ,
Because you have a relationship between the two tables,that's why the result is what you are after.
Create a slicer table as below:
Slicer table = SELECTCOLUMNS('Months',"Month",'Months'[Month],"Monthnum",'Months'[Monthnum])
Then create a measure :
Measure 3 =
var _tab=SUMMARIZE('SAP Line Items FY','SAP Line Items FY'[Fiscal Period],"value",IF(MAX('SAP Line Items FY'[Fiscal Period])<='SAP Line Items FY'[Measure 2],[Scaled Actual Amount ($AOPfx) 1],[Scaled Plan Amount ($AOPfx)1]))
Return
SUMX(_tab,[value])
And you will see:
For the related .pbix file,pls see attached.
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
Thanks for the solution! This works perfectly! 😊
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
61 | |
61 | |
55 | |
38 | |
27 |
User | Count |
---|---|
83 | |
61 | |
45 | |
41 | |
39 |