Formula adapt to current year

[Anonymous]

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)?

[Anonymous · Accepted solution]

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

[amitchandak]

@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 )