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

[andybrace]

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?

[v-kelly-msft]

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!

[andybrace]

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.

[v-kelly-msft · 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:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

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

[andybrace]

Thanks for the solution!  This works perfectly! 😊