Stop YTD measure from being filtered by date Slicer

[Anonymous]

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.

[v-jayw-msft]

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.

[Anonymous]

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:

SickLeaveYTD =

TOTALYTD (

CALCULATE (

SUM ( 'cdm_leavebanktransactions (2)'[cdm_amount] ),

FILTER (

ALL ( 'cdm_leavebanktransactions (2)' ),

'cdm_leavebanktransactions (2)'[_cdm_workerid_value]

= MAX ( 'cdm_leavebanktransactions (2)'[_cdm_workerid_value] ))),

'cdm_leavebanktransactions (2)'[cdm_transactiondate].[Date])

However, it seems to be returning a different value altogether now. Progress is progress though, thank you!

[Anonymous]

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:

   Calendar date = CALENDAR(date(2018,1,1),date(2019,12,31))

B. Create a relationship between the data table with the calendar table

  

Create calculated Measures:

YTD sick = TOTALYTD(SUM('Table'[Amount]),'Calendar date'[Date])

YTD sick no Filter = TOTALYTD(SUM('Table'[Amount]),All('Calendar date'[Date]))

Please let me know if this helps.

Tomas 

[Anonymous]

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:

SickLeaveYTD =

CALCULATE (

SUM ('cdm_leavebanktransactions (2)'[cdm_amount]),

FILTER('cdm_leaverequests (2)', 'cdm_leaverequests (2)'[cdm_status] = 754400002),

FILTER('cdm_leavetypes (2)', 'cdm_leavetypes (2)'[cdm_type] = "Sick Leave"),

DATESYTD (AutoCalendar[Date])

)

(I called it AutoCalendar because I used the CALENDARAUTO() function to create it, it's a date table)

[Anonymous]

Hi all,

A small update: I've managed to get a measure working with this code -

SickLeaveYTD =

CALCULATE(

ABS(SUM('cdm_leavebanktransactions (2)'[cdm_amount])),

DATESYTD('cdm_leavebanktransactions (2)'[cdm_transactiondate].[Date]),

'cdm_leavetypes (2)'[cdm_type] = "Sick Leave",

'cdm_leaverequests (2)'[cdm_status] = 754400002,

ALL('cdm_leavebanktransactions (2)')

)

It appears to be calculating the total correctly, but it's giving every employee that total amount.

Does anyone know how I could fix this? Thanks!

[Anonymous · Accepted 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.

Sick Leave YTD Column =

CALCULATE(

ABS(SUM('cdm_leavebanktransactions (2)'[cdm_amount])),

DATESYTD('cdm_leavebanktransactions (2)'[cdm_transactiondate].[Date]),

'cdm_leavetypes (2)'[cdm_type] = "Sick Leave",

'cdm_leaverequests (2)'[cdm_status] = 754400002

)

I added a couple of filters to ensure it returns the correct amount.

[v-jayw-msft]

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

[Anonymous]

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:

SickLeaveYTD =

TOTALYTD(

    SUM('cdm_leavebanktransactions (2)'[cdm_amount]),

    'cdm_leavebanktransactions (2)'[cdm_transactiondate].[Date])

I've inserted a screenshot of the report - it only contains test data and accounts. There's the date slicer above the table.

[kendrickp87]

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

[Anonymous]

Hi Ken,

Here's the current formula for the YTD measure: 

SickLeaveYTD =

TOTALYTD(

    SUM('cdm_leavebanktransactions (2)'[cdm_amount]),

    'cdm_leavebanktransactions (2)'[cdm_transactiondate].[Date])

The filter I was trying to use is the Slicer filter, which filters the visualisation by certain dates. This was a requirement from the HR department, as they would like the report done by specific dates.

[Anonymous]

Hi Ciara

Did you receive an answer to your question?

Tomas

[Anonymous]

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(): 

SickLeaveYTD =

TOTALYTD(

    SUM('cdm_leavebanktransactions (2)'[cdm_amount]),

    ALL('cdm_leavebanktransactions (2)'[cdm_transactiondate].[Date]))

I'm definitely missing something, any help is appreciated!

[Anonymous]

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.

SickLeaveYTD =

var _MaxDate = max(Calendar[Date])

var _CurrentYear = year(max(Calendar[Date])

return calculate( SUM('cdm_leavebanktransactions (2)'[cdm_amount]),Filter(All(Calendar),

Calendar[Year] = _CurrentYear, Calendar[Date]<= _MaxDate))

please let me know if this help

Tomas

    ALL('cdm_leavebanktransactions (2)'[cdm_transactiondate].[Date]))

[Anonymous]

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:

SickLeaveYTD =

var maxyear = year(max(CustomCalendarYTD[Date]))

var maxmonth = month(max(CustomCalendarYTD[Month Number]))

var maxdate = MAX(CustomCalendarYTD[Date])

RETURN

CALCULATE(

SUM('cdm_leavebanktransactions (2)'[cdm_amount]),

Filter(All('CustomCalendarYTD'),

FORMAT(CustomCalendarYTD[Calendar Year], YEAR(CustomCalendarYTD[Calendar Year])) = FORMAT(maxyear, YEAR(maxyear)) &

FORMAT(CustomCalendarYTD[Date], DATE(CustomCalendarYTD[Calendar Year], CustomCalendarYTD[Month Number], CustomCalendarYTD[Date]) <= FORMAT(maxdate, DATE(maxyear, maxmonth, maxdate)))))

If I find the answer on my own, I'll be sure to update the thread.