cancel
Showing results for 
Search instead for 
Did you mean: 
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
May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Submit your Data Story

Data Stories Gallery

Share your Data Story with the Community in the Data Stories Gallery.

Top Solution Authors