Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Shynel
New Member

Need help with totalytd and periods - HR data

Hi guys, I need help with my measure, that summarize FTE cumulative yearly.
I have seperate calendar to select period of time.


Measure FTE =
VAR _StartPeriod = FIRSTDATE('Calendar Periods'[Date])
VAR _EndPeriod = LASTDATE('Calendar Periods'[Date])
VAR _FTETable =
CALCULATETABLE(
SUMMARIZE(
Aggreements,
'Aggreements'[ID],
"AVGFTE",
// if there is more than one changes in agreement in a month I have to count FTE properly
AVERAGE(Aggreements[FTE])
),
// search active aggreements within selected period
Aggreements[EndDate] >= _StartPeriod,
Aggreements[StartDate] <= _EndPeriod
)
RETURN
SUMX(
_FTETable ,
[AVGFTE]
)

When I select all year in calendar slicer, the data are not summarize monthly. How should I change the measure above and group FTE montly?

 
1 ACCEPTED SOLUTION

Hi @Shynel ,

 

You can refer to the following example where I am accumulating Max as your measure:

vtianyichmsft_0-1714115767650.png

Measure 2 = var _t = ADDCOLUMNS('Table',"A",SUMX(FILTER(ALL('Table'),[ID]<=EARLIER([ID])),[Max]))
RETURN SUMX(_t,[A])

 

Hope it helps!

 

Best regards,
Community Support Team_ Scott Chang

 

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

5 REPLIES 5
Shynel
New Member

Hi @v-tianyich-msft , please take a look for my report PBIX FILE and sample data.

There is a measure that should sum up the average FTE for each month of the year.
January: 3,8
February: 3,8 + 3,8 = 7,6
March: 7,6 + 3,8 = 11,4
...
December: 50,3 - cumulative FTE for the whole year

Unfortunately it is not working today. I suspect that because I am counting the average for the period and not the total. Converting to a sum will result in multiplied values for the month (example ID 2).

Hi @Shynel ,

 

Because of the policy, I can't download your file, please show your sample data in a usable form as well as the expected results.

 

Best regards,
Community Support Team_ Scott Chang

Hi @v-tianyich-msft ,
Agreements.pngPeriods.pngVisuals.png

 

Measure FTE =
VAR _StartPeriod = FIRSTDATE('Calendar Periods'[Date])
VAR _EndPeriod = LASTDATE('Calendar Periods'[Date])
VAR _FTETable =
CALCULATETABLE(
    SUMMARIZE(
        Agreements,
        'Agreements'[ID],
        "AVGFTE",
        // if there is more than one changes in agreement in a month I have to count FTE properly
        AVERAGE(Agreements[FTE])
    ),
    // search active aggreements within selected period
    Agreements[EndDate] >= _StartPeriod,
    Agreements[StartDate] <= _EndPeriod
    )
RETURN
SUMX(
    _FTETable,
    [AVGFTE]
)
 
Measure FTE TotalYTD =
CALCULATE(
    [Measure FTE],
    DATESYTD( 'Calendar Periods'[Date])
)

 

Hi @Shynel ,

 

You can refer to the following example where I am accumulating Max as your measure:

vtianyichmsft_0-1714115767650.png

Measure 2 = var _t = ADDCOLUMNS('Table',"A",SUMX(FILTER(ALL('Table'),[ID]<=EARLIER([ID])),[Max]))
RETURN SUMX(_t,[A])

 

Hope it helps!

 

Best regards,
Community Support Team_ Scott Chang

 

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

v-tianyich-msft
Community Support
Community Support

Hi @Shynel ,

 

Not sure about your data model, so I made simple samples just to show the screening process only, the specifics need to be modified by you:

vtianyichmsft_0-1714023692171.png

 

vtianyichmsft_1-1714023709021.png

 

 

Measure = var _min =MIN('Table 2'[Date])
var _max = MAX('Table 2'[Date])
RETURN CALCULATE(COUNTAX('Table',[ID]),FILTER('Table',[Start]<=_max&&[End]>=_min))

 

An attachment for your reference. Hope it helps!

 

Best regards,
Community Support Team_ Scott Chang

 

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors