Fix end date dynamically on chart with measure

[Cobra77]

Hi,

We have 3 tables

- Worked hours 1 (wh1)

- Worked hours 2 (wh2)

- company

the 3 tables are connected to the calendar table by an id

2 measures on the graph :

- Sum month by month in the company table

- and 12 months Rolling ; who divide sum wh1 by sum wh2

On the graph power bi , users want the chart to stop at the last date recorded in the company table.

the results are good without try to stop the date on chart.

measure 1 := CALCULATE(DIVIDE(wh1[nb];wh2[nb]);DATESBETWEEN ( '00 - Calendar'[04 - Date];
        NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE ( '00 - Calendar'[04 - Date] ) ) );
        LASTDATE ( '00 - Calendar'[04 - Date] )
    )

measure 2 := SUM( company(rate))

For the measure 2 if i test with  : CALCULATE(SUM(vf_ObjectifsSociete[tf]);'00 - Calendar'[Id]<20171201) 

This seems good

But not with the measure 1 ?

limit november 2017

without filter on time dimension in power bi

dynamically with the data in the company table

An idea ?

thanks for your help

[v-yuezhe-msft · Accepted solution]

@Cobra77,

Try to use the following DAX and check if you get expected result.

measure 1 =
VAR LastDate = LASTDATE ( '00 - Calendar'[04 - Date] )
RETURN IF (
    DATE(2017;12;1) >= FIRSTDATE ('00 - Calendar'[04 - Date]);
    CALCULATE (
        DIVIDE(wh1[nb];wh2[nb]);
        ALL('00 - Calendar'[04 - Date]);
        DATESBETWEEN (
            '00 - Calendar'[04 - Date];
            NEXTDAY ( SAMEPERIODLASTYEAR ( LastDate ) );
            LastDate
        )
    )
)

Regards,
Lydia