Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I have written a long measure which has worked as expected. A the measure is quite long and some var is used in other measures as well, I decide to take out var weekstart_ and var weekend_ and convert them into measure [weekstart] & [weekend]. I them quote these two measures in a new measure. However, the new measure [Total Hrs Planned Weekly2] doesn't work here. It doesn't filter data with [weekstart] and [weekend] values. Please help.
Solved! Go to Solution.
Hi, @Jeanxyz ;
You could modify the measure as follows:
weekstart =
VAR year_ = CALCULATE(MAX ( Dim_Date[Year] ),ALLSELECTED(Dim_Date))
VAR week_ = CALCULATE(MAX ( Dim_Date[WeekNumber] ),ALLSELECTED('Dim_Date'))
VAR weekstart_ =
IF (
CALCULATE(MIN (Dim_Date[DayOfWeekNumber]),
FILTER (
ALL ( Dim_Date ),
Dim_Date[Year] = year_
&& Dim_Date[WeekNumber] = week_)) > 1,
CALCULATE (
MIN ( Dim_Date[Date] ),
FILTER (
ALL ( Dim_Date ),
Dim_Date[Year] = year_ - 1
&& Dim_Date[WeekNumber] = 53 )),
CALCULATE (
MIN ( Dim_Date[Date] ),
FILTER (
ALL ( Dim_Date ),
Dim_Date[Year] = year_
&& Dim_Date[WeekNumber] = week_) ))
RETURN weekstart_
weekend =
VAR year_ =
CALCULATE(MAX ( Dim_Date[Year] ),ALLSELECTED(Dim_Date))
VAR week_ =
CALCULATE(MAX ( Dim_Date[WeekNumber] ),ALLSELECTED(Dim_Date))
VAR weekend_ =
CALCULATE( IF (
CALCULATE (
MAX ( Dim_Date[DayOfWeekNumber] ),
FILTER (
ALL ( Dim_Date ),
Dim_Date[Year] = year_
&& Dim_Date[WeekNumber] = week_)) < 7,
CALCULATE (
MAX ( Dim_Date[Date] ),
FILTER (
ALL ( Dim_Date ),
Dim_Date[Year] = year_ + 1
&& Dim_Date[WeekNumber] = 1 ) ),
CALCULATE(MAX ( Dim_Date[Date]),
FILTER (
ALL ( Dim_Date ),
Dim_Date[Year] = year_
&& Dim_Date[WeekNumber] = week_) ) ))
RETURN weekend_
Total Hrs Planned Weekly =
VAR year_ =
SELECTEDVALUE ( Dim_Date[Year] )
VAR current_year_ =
YEAR ( TODAY () )
VAR max_week_ =
SWITCH (
TRUE (),
year_ < current_year_, MAX ( Dim_Date[WeekNumber] ),
MAXX (
FILTER ( ALL ( Dim_Date ), Dim_Date[Date] = TODAY () ),
Dim_Date[WeekNumber]
)
)
VAR total_records =
CALCULATE (
SUM ( Fact_Timesheets[Planned Working Time] ),
FILTER (
ALL ( Dim_Date ),
Dim_Date[Date] >= MAXX ( SUMMARIZE ( 'Dim_Date', "1", [weekstart] ), [1] )
&& Dim_Date[Date] <= MAXX ( SUMMARIZE ( 'Dim_Date', "1", [weekend] ), [1] )
&& Dim_Date[WeekNumber] < max_week_
),
Fact_Timesheets[Time Category ID] <> 12
)
RETURN
total_records
The final output is shown below:
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @Jeanxyz ;
Is your problem solved?? If so, Would you mind accept the helpful replies as solutions? Then we are able to close the thread. More people who have the same requirement will find the solution quickly and benefit here. Thank you.
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @Jeanxyz ;
You could modify the measure as follows:
weekstart =
VAR year_ = CALCULATE(MAX ( Dim_Date[Year] ),ALLSELECTED(Dim_Date))
VAR week_ = CALCULATE(MAX ( Dim_Date[WeekNumber] ),ALLSELECTED('Dim_Date'))
VAR weekstart_ =
IF (
CALCULATE(MIN (Dim_Date[DayOfWeekNumber]),
FILTER (
ALL ( Dim_Date ),
Dim_Date[Year] = year_
&& Dim_Date[WeekNumber] = week_)) > 1,
CALCULATE (
MIN ( Dim_Date[Date] ),
FILTER (
ALL ( Dim_Date ),
Dim_Date[Year] = year_ - 1
&& Dim_Date[WeekNumber] = 53 )),
CALCULATE (
MIN ( Dim_Date[Date] ),
FILTER (
ALL ( Dim_Date ),
Dim_Date[Year] = year_
&& Dim_Date[WeekNumber] = week_) ))
RETURN weekstart_
weekend =
VAR year_ =
CALCULATE(MAX ( Dim_Date[Year] ),ALLSELECTED(Dim_Date))
VAR week_ =
CALCULATE(MAX ( Dim_Date[WeekNumber] ),ALLSELECTED(Dim_Date))
VAR weekend_ =
CALCULATE( IF (
CALCULATE (
MAX ( Dim_Date[DayOfWeekNumber] ),
FILTER (
ALL ( Dim_Date ),
Dim_Date[Year] = year_
&& Dim_Date[WeekNumber] = week_)) < 7,
CALCULATE (
MAX ( Dim_Date[Date] ),
FILTER (
ALL ( Dim_Date ),
Dim_Date[Year] = year_ + 1
&& Dim_Date[WeekNumber] = 1 ) ),
CALCULATE(MAX ( Dim_Date[Date]),
FILTER (
ALL ( Dim_Date ),
Dim_Date[Year] = year_
&& Dim_Date[WeekNumber] = week_) ) ))
RETURN weekend_
Total Hrs Planned Weekly =
VAR year_ =
SELECTEDVALUE ( Dim_Date[Year] )
VAR current_year_ =
YEAR ( TODAY () )
VAR max_week_ =
SWITCH (
TRUE (),
year_ < current_year_, MAX ( Dim_Date[WeekNumber] ),
MAXX (
FILTER ( ALL ( Dim_Date ), Dim_Date[Date] = TODAY () ),
Dim_Date[WeekNumber]
)
)
VAR total_records =
CALCULATE (
SUM ( Fact_Timesheets[Planned Working Time] ),
FILTER (
ALL ( Dim_Date ),
Dim_Date[Date] >= MAXX ( SUMMARIZE ( 'Dim_Date', "1", [weekstart] ), [1] )
&& Dim_Date[Date] <= MAXX ( SUMMARIZE ( 'Dim_Date', "1", [weekend] ), [1] )
&& Dim_Date[WeekNumber] < max_week_
),
Fact_Timesheets[Time Category ID] <> 12
)
RETURN
total_records
The final output is shown below:
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Here is a printscreen of the result, the new measure ignores week filter and resource ID (employee id). It sums up all hours of the select year month.
when you say it doesn't work you haven't presented what the issue is ?
Proud to be a Super User!
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |