Use slicer value in Dax Measure

[Saskwyt]

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

[ChiragGarg2512]

@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]

Hi @ChiragGarg2512 I can't seem to get this to work...Thank you for your response though 🙂

[Saskwyt]

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 · Accepted solution]

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-me...

[Saskwyt]

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

[mlsx4]

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...