topic Re: Please help me with this dax forumle in DAX Commands and Tips

Please help me with this dax forumle

Anonymous — Fri, 21 Oct 2022 09:25:19 GMT

Hello guys I need these calculation to work for a card visual to display the value of sum auditfile

CALCULATE(SUM('2 (Auditfile)'[saldo]), IF(VALUES('2 (Auditfile)'[fiscalYear])>2018,[fiscalyear]-1))

why doesnt it work i want the value of sum saldo

many thanks :):):)

Re: Please help me with this dax forumle

tamerj1 — Fri, 21 Oct 2022 09:55:23 GMT

Hi Anonymous

would you please explain what are you trying to achieve by the IF statement in your DAX code?

Re: Please help me with this dax forumle

Anonymous — Fri, 21 Oct 2022 10:00:46 GMT

Hey tamerj1

i'm trying to archieve a calculation in a card visual, if i select 2019 in a slicer i want the sum of saldo from 2018
if i select 2020 in a slicer i want the sum of saldo from 2019 and so on

i know the if statement works for -1 on the year

Re: Please help me with this dax forumle

Anonymous — Fri, 21 Oct 2022 10:16:22 GMT

Hello why not use previous year?

Calculate(sum(tabble[value]),previousyear([date]))

Re: Please help me with this dax forumle

Anonymous — Fri, 21 Oct 2022 10:34:12 GMT

becasue that doesnt work for me the way my data is build :(:(:( it would be the easy way i know but i have the bad luck i have to fix a working repot :S

Re: Please help me with this dax forumle

Anonymous — Fri, 21 Oct 2022 10:44:14 GMT

Hmm well when you select a slicer the range becomes only for the slicer so try the removefilters function?

CALCULATE(SUM('2 (Auditfile)'[saldo]), IF(VALUES('2 (Auditfile)'[fiscalYear])>2018,[fiscalyear]-1),removefilters(dateyouuseforslicer))

Re: Please help me with this dax forumle

Anonymous — Fri, 21 Oct 2022 11:12:39 GMT

if i use your formule i get this error

Re: Please help me with this dax forumle

tamerj1 — Fri, 21 Oct 2022 11:31:37 GMT

Hi Anonymous

you can manually insert a single column table that contains the years. Let's call it 'YearFilter' which will be completely disconnected from any other table in your data model. This table will be used as a slicer and the measure would be

SumMeaaure = VAR CurrentYear = SELECTEDVALUE ( 'YearFilter'[Year] ) RETURN CALCULATE ( SUM ( '2 (Auditfile)'[saldo] ), '2 (Auditfile)'[fiscalYear] >= CurrentYear - 1 )