cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Jeanxyz
Helper III
Helper III

can't wrap a measure in another measure

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.

 

original measure:
Total Hrs Planned Weekly2 =
VAR year_ = SELECTEDVALUE( Dim_Date[Year] )
VAR week_ = MAX ( Dim_Date[WeekNumber] )
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 weekstart_ = if(
MINX (FILTER (all(Dim_Date), Dim_Date[Year] = year_ && Dim_Date[WeekNumber] = week_ ),
Dim_Date[DayOfWeekNumber])>1,MINX (FILTER (all(Dim_Date), Dim_Date[Year] = year_ -1 && Dim_Date[WeekNumber] = 53 ), Dim_Date[Date]),
MINX (FILTER (all(Dim_Date), Dim_Date[Year] = year_ && Dim_Date[WeekNumber] = week_ ),Dim_Date[Date])
)
VAR weekend_ = if(
MAXX (FILTER (all(Dim_Date), Dim_Date[Year] = year_ && Dim_Date[WeekNumber] = week_ ),
Dim_Date[DayOfWeekNumber])<7,MAXX (FILTER (all(Dim_Date), Dim_Date[Year] = year_ +1 && Dim_Date[WeekNumber] = 1 ), Dim_Date[Date]),
MAXX (FILTER (all(Dim_Date), Dim_Date[Year] = year_ && Dim_Date[WeekNumber] = week_ ),Dim_Date[Date])
)
var total_records=calculate(sum(Fact_Timesheets[Planned Working Time]),filter(all(Dim_Date), Dim_Date[Date]>=weekstart_ && Dim_Date[Date]<=weekend_ && Dim_Date[WeekNumber]< max_week_),Fact_Timesheets[Time Category ID]<>12)
RETURN
total_records
 
New measures
weekstart =
VAR year_ = MAX ( Dim_Date[Year] )
VAR week_ = MAX ( Dim_Date[WeekNumber] )
VAR weekstart_ = calculate(if(
MINX (FILTER (all(Dim_Date), Dim_Date[Year] = year_ && Dim_Date[WeekNumber] = week_ ),
Dim_Date[DayOfWeekNumber])>1,
MINX (FILTER (all(Dim_Date), Dim_Date[Year] = year_ -1 && Dim_Date[WeekNumber] = 53 ), Dim_Date[Date]),
MINX (FILTER (all(Dim_Date), Dim_Date[Year] = year_ && Dim_Date[WeekNumber] = week_ ),Dim_Date[Date])
))
RETURN
weekstart_
*****************************************
weekend =
VAR year_ = MAX ( Dim_Date[Year] )
VAR week_ = MAX ( Dim_Date[WeekNumber] )
VAR weekend_ = calculate(if(
MAXX (FILTER (all(Dim_Date), Dim_Date[Year] = year_ && Dim_Date[WeekNumber] = week_ ),
Dim_Date[DayOfWeekNumber])<7,MAXX (FILTER (all(Dim_Date), Dim_Date[Year] = year_ +1 && Dim_Date[WeekNumber] = 1 ), Dim_Date[Date]),
MAXX (FILTER (all(Dim_Date), Dim_Date[Year] = year_ && Dim_Date[WeekNumber] = week_ ),Dim_Date[Date])
))
//if last week of the year spans across two years, get the first day of previous year
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]>=[weekstart] && Dim_Date[Date]<=[weekend] && Dim_Date[WeekNumber]< max_week_),Fact_Timesheets[Time Category ID]<>12)
total_records
 
 
1 ACCEPTED SOLUTION
v-yalanwu-msft
Community Support
Community Support

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:

vyalanwumsft_0-1632208012298.png

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.

View solution in original post

4 REPLIES 4
v-yalanwu-msft
Community Support
Community Support

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.

v-yalanwu-msft
Community Support
Community Support

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:

vyalanwumsft_0-1632208012298.png

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.

View solution in original post

Jeanxyz
Helper III
Helper III

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.

 

wrap formula.PNG

vanessafvg
Super User
Super User

when you say it doesn't work you haven't presented what the issue is ?





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.