Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all,
I'm editing a Power BI report so that members of our HR team gain more value from it, and I'm currently having an issue with a Year-To-Date measure being filtered by the date Slicer function. Is it possible to stop this from happening? I would like to keep the YTD measure as part of the visualisation that is being filtered by the Slicer, if this is possible.
Solved! Go to Solution.
Hello all,
I've finally figured it out! Here's the code I used to ensure that my YTD column (I changed it from a measure to a column) won't be affected by the date slicer.
Hi @CiaraL,
Sorry for the delay.
In you scenario, we should use all() function when sum the account.
Please refer to the measure below to modify your measure and see if the result achieve your expectation:
SickLeaveYTD =
TOTALYTD (
CALCULATE (
SUM ( 'cdm_leavebanktransactions (2)'[cdm_amount] ),
FILTER (
ALL ( 'cdm_leavebanktransactions (2)' ),
'cdm_leavebanktransactions (2)'[name]
= MAX ( 'cdm_leavebanktransactions (2)'[name] )
)
),
'cdm_leavebanktransactions (2)'[cdm_transactiondate].[Date]
)
Here’s my sample data and the result would be shown as below:
BTW, Pbix as attached, hopefully works for you.
Best Regards,
Jay
Community Support Team _ Jay Wang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Jay,
Sorry for the delay in my reponse, was out sick. I tried the code you gave me, and replaced [name] with [_cdm_workerid_value], resulting with me using this:
Hi Ciara
If I understand correctly, you are trying to get the Year to Date for the latest year disregarding the calendar slicer. If that is then this will.
A. I create a Calendar table as such:
YTD sick = TOTALYTD(SUM('Table'[Amount]),'Calendar date'[Date])
YTD sick no Filter = TOTALYTD(SUM('Table'[Amount]),All('Calendar date'[Date]))
Hi Tomas,
Thank you, this helped a lot! Making a custom calendar seemed to do the trick - unfortunately there is a slight discrepancy that I'll investigate, but here's the code that got me this far:
Hi all,
A small update: I've managed to get a measure working with this code -
Hello all,
I've finally figured it out! Here's the code I used to ensure that my YTD column (I changed it from a measure to a column) won't be affected by the date slicer.
Hi @Anonymous ,
Normally, we can use All() funtion to avoid table be filtered by slicer but sometimes the case is complicated.
Please share some sample data to us if you don't have any Confidential Information.
In that case we might be able to help you more effectively.
Best Regards,
Jay
Hi Jay,
I had some difficulty with the ALL() function, hopefully I'll have more luck with it.
Here's the formula for the YTD column:
Hi @Anonymous ,
It absolutely is. Could you please share the current formula & provide more information around your filters? is it Year, Month or date?
In my reports, I have a year and month filter. Here are a few examples of the formulas I am using;
MTD Budget = TOTALMTD(
SUM('Expense Data'[BUDGET]),
'Calendar'[Date])
YTD Budget = TOTALYTD(
SUM('Expense Data'[BUDGET]),
'Calendar'[Date])
Annual Budget = CALCULATE(
SUM('Expense Data'[BUDGET]),
FILTER(ALL('Calendar'),
'Calendar'[Year]=MAX('Calendar'[Year])
)
)
Exp Budget by Month = CALCULATE(
SUM('Expense Data'[BUDGET]),
ALL('Calendar'[Month]))
Best,
Ken
Hi Ken,
Here's the current formula for the YTD measure:
Hi Ciara
Did you receive an answer to your question?
Tomas
Hi Tomas,
I've tried to add the ALL() function to the YTD measure formula, but unfortunately the measure is still being affected by the Slicer.
Formula with ALL():
Hi Ciara
I notice that you are using the build-in automatic calendar, I would recommend creating your own calendar table.
See link https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
Then I would create a relationship between the calendar and your table.
Hi Tomas,
I followed your advice, unfortunately it's throwing an error when I try to compare the date values.
I've tried to convert the values with FORMAT(), however it doesn't seem to be making too much of a difference - still running into this error.
Here's my current formula:
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
96 | |
75 | |
63 | |
62 |
User | Count |
---|---|
135 | |
105 | |
104 | |
80 | |
65 |