topic Re: YTD with allexcept in DAX Commands and Tips

YTD with allexcept

Anonymous — Mon, 06 Sep 2021 06:55:22 GMT

Hi all,

so I have a measure here that gives me the correct monthly value where i do need to use allexcept because otherwise it wont filter the departments but I need to do a YTD of it. When I tried to use ytd of it, it wont show correctly. How do I get YTD with alllexcept?

x=CALCULATE (

SUM ( 'Budget'[Budget] )/3 ,ALLEXCEPT('Budget',''Budget'[Department]),datesytd('calendar','Date'[date])

Re: YTD with allexcept

Fowmy — Mon, 06 Sep 2021 07:08:10 GMT

Anonymous

For DATESYTD, you need to provide your date column from your dates table.

X = CALCULATE ( SUM ( 'Budget'[Budget] ) / 3, ALLEXCEPT ( 'Budget', 'Budget'[Department] ), DATESYTD ( 'Date'[date] ) )

Re: YTD with allexcept

Anonymous — Mon, 06 Sep 2021 07:11:47 GMT

thanks Fowmy but it still wont work because the issue is that i have all the values in the month december but i need to divide that to get the number i need but if i do the ytd function wont show anything?

Re: YTD with allexcept

Fowmy — Mon, 06 Sep 2021 07:27:45 GMT

Anonymous

It's hard to guess what type of data model and the report you have. You can share a sample Power BI file and explain the expected result if the following measure doesn't work for you.

X = CALCULATE ( CALCULATE ( SUM ( 'Budget'[Budget] ) / 3, VALUES ( 'Dates'[Year] ), REMOVEFILTERS ( Dates ) ), ALLEXCEPT ( 'Budget', 'Budget'[Department] ), DATESYTD ( 'Dates'[date] ) )

Re: YTD with allexcept

Anonymous — Tue, 07 Sep 2021 07:03:58 GMT

Hi Fowmy ,

I just uploaded a file here https://github.com/userdata21/test

the issue is that ytd measure is not working as i want there the cumulatief. Any ideas?