Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
andybrace
Frequent Visitor

DAX Combined Actual/Plan dataset using slicer to select current month

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 

Measure = VAR mthnum = SELECTEDVALUE(Months[Monthnum]) RETURN SUMX(FILTER('SAP Line Items FY','SAP Line Items FY'[Fiscal Period]<=mthnum),[Scaled Actual Amount ($AOPfx)])+SUMX(FILTER('SAP Line Items FY','SAP Line Items FY'[Fiscal Period]>mthnum),[Scaled Plan Amount ($AOPfx)])
 
But it seems to use [Scaled Plan Amount ($AOPfx)] for all periods.  However if I hard code the mthnum to 1 like this:
Measure = VAR mthnum = SELECTEDVALUE(Months[Monthnum]) RETURN SUMX(FILTER('SAP Line Items FY','SAP Line Items FY'[Fiscal Period]<=1),[Scaled Actual Amount ($AOPfx)])+SUMX(FILTER('SAP Line Items FY','SAP Line Items FY'[Fiscal Period]>1),[Scaled Plan Amount ($AOPfx)])
 
Then it works as expected.  I assumed it was a datatype issue, maybe Monthnum or Fiscal Period being a text field, but they are both "Whole number".

Any ideas why it won't work or alternative approaches?
1 ACCEPTED 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:

vkellymsft_0-1627034893528.png

vkellymsft_1-1627034909991.png

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

 

View solution in original post

4 REPLIES 4
v-kelly-msft
Community Support
Community Support

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 

VAR mthnum = 2
Whereas I want this to use the Month selected in the slicer to determine the mthnum.

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:

vkellymsft_0-1627034893528.png

vkellymsft_1-1627034909991.png

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! 😊

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.