how to simplify and optimize this measure

[Cobra77]

Hi,

We have a report with a slicer Year ( multiple selected values possible )

Sample : rule if day ( today() ) <= 15  take month before to calcul or > 15 take all

if user selected only 2019 :

it is the 2th of april 2019 :   i must calcul from 01/01/2019 to 31/03/2019

From  april 16th : i must calcul from 01/01/2019 to 16/04/2019

if user selected only 2018 : i take all 2018

if user take 2018 and 2017 : i take all 2017 and 2018

if user selected 2018 and  2019 :

it is the 5th of May 2019 :   i must calcul from 01/01/2018 to 30/04/2019

from May 18th : i must calcul from 01/01/2018 to 18/05/2019

i begin with this measure , does not work in all cases :

TF (15) = IF (  DAY(TODAY()) > 15

; DIVIDE(([Nb ATAA]+[Nb ATM])*1000000;[Nb Heures Travaillées])

; IF ( HASONEVALUE('01 - Calendrier'[01 - Année]) = FALSE() && DAY(TODAY()) > 15 ; DIVIDE(([Nb ATAA]+[Nb ATM])*1000000;[Nb Heures Travaillées])

; IF (DAY(TODAY()) <= 15 && HASONEVALUE('01 - Calendrier'[01 - Année] ) && VALUES('01 - Calendrier'[01 - Année]) = YEAR(TODAY())

        ; CALCULATE(DIVIDE(([Nb ATAA]+[Nb ATM])*1000000;[Nb Heures Travaillées])

                    ; CALCULATETABLE('01 - Calendrier';'01 - Calendrier'[MoisNum] >=1 && '01 - Calendrier'[MoisNum] < MONTH(TODAY())))        

        ; BLANK() )))

thanks for your helps.

[v-frfei-msft]

Hi @Cobra77 ,

Hope this bolg could help you. Or you can share your sample file to me via One Drive.

[Cobra77]

Hi @v-frfei-msft 

sorry for the delay and my english

https://1drv.ms/f/s!AmluE3Tt6h4WehgxHsRvp3KJ2Zs

Thanks

[v-frfei-msft · Accepted solution]

Hi @Cobra77 ,

We can create calculated column in Calendar table firstly.

YearMonth = YEAR('Calendar'[Date])*100 + MONTH('Calendar'[Date])

Then create a measure as below.

Measure = 
VAR ym =
    YEAR ( TODAY () ) * 100
        + MONTH ( TODAY () )
VAR a =
    DIVIDE ( [Total Accident] * 1000000, [Total wh] )
RETURN
    IF (
        MAX ( 'Calendar'[Annee] ) = 2019
            && DAY ( TODAY () ) <= 15,
        CALCULATE (
            a,
            FILTER ( ALLSELECTED ( 'Calendar' ), 'Calendar'[YearMonth] < ym )
        ),
        IF (
            MAX ( 'Calendar'[Annee] ) = 2019
                && DAY ( TODAY () ) > 15,
            CALCULATE (
                a,
                FILTER ( ALLSELECTED ( 'Calendar' ), 'Calendar'[Date] <= TODAY () )
            ),
            IF (
                MAX ( 'Calendar'[Annee] ) <> 2019,
                CALCULATE ( a, ALLSELECTED ( 'Calendar' ) )
            )
        )
    )