cancel
Showing results for
Did you mean:
Highlighted
Member

## Fix end date dynamically on chart with measure

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 ?

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator

## Re: Fix end date dynamically on chart with measure

@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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Moderator

## Re: Fix end date dynamically on chart with measure

@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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

#### Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

#### Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)