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
 
 
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.

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
Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Microsoft Ignite 768x460.png

Find your focus

Explore the latest tools,training sessions,technical expertise, networking and more.

Power BI Womens Summit 2021 768 x460.jpg

Interviews, learning sessions, allies, and more!

#PowerBIWomenSummit

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!