Use slicer value in Dax Measure

Hi there,

Please see attached a basic PBIX model showing a sales table, a budget table and a date table. I'm trying to work out how to adjust the DAX for the measures All_Sales and All_Budget.

Sales vs Budget.pbix

The measures are currently as follows:

All_Sales =

Calculate(

SUM(Tbl_Sales[Sales]),

Filter(Date_Table,Date_Table[Date] <= Date(2023,6,30))

)

All_Budget =

Calculate(

SUM(Tbl_Budget[Budget]),

Filter(Date_Table,Date_Table[EOM] > Date(2023,6,30))

I'm trying to change the DAX so that the "Date(2023,6,30)" is replaced by the value I choose in my Date slicer.

Can someone help me achieve this please?

Thanks in advance

mlsx4
3 years ago
Hi Saskwyt

I'm afraid then you will need to create some kind of a flag. I will give you a thread similar of what I'm thinking about: https://community.fabric.microsoft.com/t5/Desktop/Date-flag-based-on-slicer-selection-and-Dynamic-measure-values/m-p/1485968#M617448

6 Replies

mlsx4

Memorable Member

3 years ago

Hi Saskwyt

I cannot see the pbix, but you can do:

All_Sales = 
  var _max = maxx(ALLSELECTED('Date_Table'),'Date_Table'[Date])
RETURN 
  CALCULATE(SUM(Tbl_Sales[Sales], FILTER(ALL('Date_Table'),'Date_Table'[Date]<=_max))

Same for budget...

Saskwyt
Frequent Visitor
3 years ago
Hey mlsx4 thanks for the feedback. See below an image of the table I'm trying to achieve - This is what my current Measures are returning. It looks like your suggested solution gives the total up to the date but doesn't show it month by month as shown below:
Hope that makes more sense!
- mlsx4
  Memorable Member
  3 years ago
  Hi Saskwyt
  
  I'm afraid then you will need to create some kind of a flag. I will give you a thread similar of what I'm thinking about: https://community.fabric.microsoft.com/t5/Desktop/Date-flag-based-on-slicer-selection-and-Dynamic-measure-values/m-p/1485968#M617448
  - Saskwyt
    Frequent Visitor
    3 years ago
    Hey mlsx4 I've managed to apply the information in the link to achieve what I was looking for, I've even managed to get a total column! There is one minor issue....the totals on the table are not correct 😞
    The measure I've used for Sales_£ is as follows:
    Sales_£ = VAR date1 = SELECTEDVALUE ( 'Table'[EOM] ) VAR date2 = SELECTEDVALUE ( 'Calendar'[EOM] ) RETURN IF ( date2 <= date1, Sum(Tbl_Sales[Sales]), 0 )
    Hope the above makes sense!!
ChiragGarg2512
Solution Sage
3 years ago
Saskwyt , The function selectedvalue should help in this condition.
All_Sales =
Calculate(
SUM(Tbl_Sales[Sales]),
Date_Table[Date] <= SELECTEDVALUE(Date_Table[Date])
)

This should take the sum of sales till the date on slicer.
- Saskwyt
  Frequent Visitor
  3 years ago
  Hi ChiragGarg2512 I can't seem to get this to work...Thank you for your response though 🙂

Forum Discussion

Use slicer value in Dax Measure

6 Replies

Related content

Set measure value based on slicer selection

SELECTEDVALUE measure from slicer keeps returning blank value when used on another measure

Measure incorporating slicer value

Slicer on measures

Wrap slicer values