Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. 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! 😊
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
143 | |
77 | |
63 | |
51 | |
47 |
User | Count |
---|---|
212 | |
82 | |
61 | |
60 | |
57 |