topic Re: Formula adapt to current year in DAX Commands and Tips

Formula adapt to current year

Anonymous — Mon, 30 May 2022 12:37:25 GMT

Hey Guys,

I need help with the following formula:

Toolbox vereist 2 = CALCULATE(IF( YEAR (MAX(Datum[Datum]))= YEAR( NOW()),

10/12 * ((CALCULATE(DISTINCTCOUNT(Datum[Maand]), personen[afdeling] = "BPM" || personen[afdeling] = "Asfalt" )) *

Calculate(DISTINCTCOUNT(Personen[Naam]), personen[afdeling] = "BPM" || personen[afdeling] = "Asfalt" )) *

[Huidige dag vh jaar] / 356,

10/12 * ((CALCULATE(DISTINCTCOUNT(Datum[Maand]), personen[afdeling] = "BPM" || personen[afdeling] = "Asfalt" )) *

Calculate(DISTINCTCOUNT(Personen[Naam]), personen[afdeling] = "BPM" || personen[afdeling] = "Asfalt" ))),

Datum[Datum] > DATE(2020,1,1), Datum[Datum] < DATE(YEAR(TODAY())+1,1,1)

)

Is it possible to make the formula that if i select the current year, that it really shows me the value up untill today (or this month)?

Re: Formula adapt to current year

amitchandak — Mon, 30 May 2022 12:58:38 GMT

Anonymous ,

New Measure =

var _fil = if( year(selectedvalue(Datum[Datum])), Year(Today()) , filter(Datum, Datum[Datum] <= Today()) ,filter(Datum, Datum[Datum] = selectedvalue(Datum[Datum])) )

return

calculate([Measure], _fil )

Re: Formula adapt to current year

Anonymous — Thu, 02 Jun 2022 05:45:54 GMT

Hi Anonymous ,

I created some data:

Here are the steps you can follow：

1. Create calculated table.

Table 2 = CALENDAR(MIN('Table'[Date]),MAX('Table'[Date]))

2. Create measure.

Measure_today = var _select=SELECTEDVALUE('Table 2'[Year]) return IF( _select = YEAR(TODAY()), CALCULATE(SUM('Table'[Amount]),FILTER(ALL('Table'),'Table'[Date]>=DATE(2020,1,1)&&'Table'[Date]<=TODAY()&&'Table'[afdeling] in {"BPM","Asfalt"})))

Measure_Month = var _select=SELECTEDVALUE('Table 2'[Year]) return IF( _select = YEAR(TODAY()), CALCULATE(SUM('Table'[Amount]),FILTER(ALL('Table'),'Table'[Date]>=DATE(2020,1,1)&&'Table'[Date]<=EOMONTH(TODAY(),0)&&'Table'[afdeling] in {"BPM","Asfalt"})))

3. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly