Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.