Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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?
Solved! Go to Solution.
Hi @Shynel ,
You can refer to the following example where I am accumulating Max as your measure:
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.
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 ,
Hi @Shynel ,
You can refer to the following example where I am accumulating Max as your measure:
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.
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:
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.
Hi, this attachment is very valuable for me. - Could you demonstrate how to create the measure for average FTE per month, too? That would be very helpful! - katina
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
32 | |
27 | |
24 | |
20 | |
16 |
User | Count |
---|---|
55 | |
38 | |
27 | |
24 | |
21 |