Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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
@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
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |