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 all,
Could anyone helt me with this measure creating?
1) I have to calculate average daily time of cargoes;
2) When check if calculated average daily time is less than target time (daily basis);
3) Then calculate how many days in month the target time was met;
Solved! Go to Solution.
Hi @KristinaSp ,
Here are the steps you can follow:
1. Create calculated column.
Count = COUNTAX(FILTER( ALL('Main_Table'),'Main_Table'[Date]=EARLIER('Main_Table'[Date])),[Date])
2. Create measure.
Averge daily time(minutes) =
var _1=CALCULATE(SUM('Main_Table'[Time(minutes)]),FILTER(ALL(Main_Table),'Main_Table'[Date]=MAX('Main_Table'[Date])))
return
IF(
MAX('Main_Table'[Count])=1,_1,
_1 / 2)
Target time ls met measure =
IF(
[Averge daily time(minutes)] >MAX('Main_Table'[Target time(minutes)]),"No","Yes")
Days number when target time is met =
var _summarize=SUMMARIZE('Main_Table',Main_Table[Date],"1",'Main_Table'[Averge daily time(minutes)],"2",'Main_Table'[Target time ls met measure])
return
COUNTX(FILTER(_summarize,YEAR([Date])=YEAR(MAX([Date]))&&MONTH([Date])=MONTH(MAX([Date]))&&[2]="Yes"),[2])
Total days =
var _summarize=SUMMARIZE('Main_Table',Main_Table[Date],"1",'Main_Table'[Averge daily time(minutes)],"2",'Main_Table'[Target time ls met measure])
return
COUNTX(FILTER(_summarize,YEAR([Date])=YEAR(MAX([Date]))&&MONTH([Date])=MONTH(MAX([Date]))),[2])
3. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi @KristinaSp ,
Here are the steps you can follow:
1. Create calculated column.
Count = COUNTAX(FILTER( ALL('Main_Table'),'Main_Table'[Date]=EARLIER('Main_Table'[Date])),[Date])
2. Create measure.
Averge daily time(minutes) =
var _1=CALCULATE(SUM('Main_Table'[Time(minutes)]),FILTER(ALL(Main_Table),'Main_Table'[Date]=MAX('Main_Table'[Date])))
return
IF(
MAX('Main_Table'[Count])=1,_1,
_1 / 2)
Target time ls met measure =
IF(
[Averge daily time(minutes)] >MAX('Main_Table'[Target time(minutes)]),"No","Yes")
Days number when target time is met =
var _summarize=SUMMARIZE('Main_Table',Main_Table[Date],"1",'Main_Table'[Averge daily time(minutes)],"2",'Main_Table'[Target time ls met measure])
return
COUNTX(FILTER(_summarize,YEAR([Date])=YEAR(MAX([Date]))&&MONTH([Date])=MONTH(MAX([Date]))&&[2]="Yes"),[2])
Total days =
var _summarize=SUMMARIZE('Main_Table',Main_Table[Date],"1",'Main_Table'[Averge daily time(minutes)],"2",'Main_Table'[Target time ls met measure])
return
COUNTX(FILTER(_summarize,YEAR([Date])=YEAR(MAX([Date]))&&MONTH([Date])=MONTH(MAX([Date]))),[2])
3. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
@KristinaSp , Create measures like
Actual = sum(MainTable[Time in Min])
Target = sum(Target[Time in Min])
Met = sumx(values('Date'[Date]), calculate(if([Actual]>[Target],1, 0)))
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.
User | Count |
---|---|
86 | |
84 | |
66 | |
62 | |
60 |
User | Count |
---|---|
197 | |
118 | |
108 | |
78 | |
69 |