Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello everibody.
wishing you and your family are good and healthy, i want to know if some of you could help me with my necesity.
right now i need to stash some time objetives on customer visits and i want to have this dynamics.
My Code today is:
but i want to have the actual target respect with the day of the year or day of the month or day of the Q, use fixed just when we need to see the past.
also i want to have the correct total of the sum in the objetiv in Matrix Visual.
i will really apreciate your help and support.
Solved! Go to Solution.
Hi @MQUINT-07 ,
Please have a try.
Objetivos de Visita =
VAR IMT =
SELECTEDVALUE ( BU[BG Fix] ) = "IQS"
RETURN
VAR MED =
SELECTEDVALUE ( BU[BG Fix] ) = "MED"
RETURN
VAR MIC =
SELECTEDVALUE ( BU[BG Fix] ) = "RMS"
RETURN
VAR ObjetivoMes =
HASONEFILTER ( 'End_Date'[FiscalYear] )
&& HASONEFILTER ( End_Date[FiscalQuarter] )
&& HASONEFILTER ( End_Date[MonthName] )
RETURN
VAR ObjetivoQ =
HASONEFILTER ( 'End_Date'[FiscalYear] )
&& HASONEFILTER ( End_Date[FiscalQuarter] )
RETURN
VAR ObjetivoFY =
HASONEFILTER ( 'End_Date'[FiscalYear] )
RETURN
IF (
IMT && ObjetivoMes,
32,
IF (
IMT && ObjetivoQ,
96,
IF (
IMT && ObjetivoFY,
384,
IF (
MED && ObjetivoMes,
80,
IF (
MED && ObjetivoQ,
240,
IF (
MED && ObjetivoFY,
960,
IF (
MIC && ObjetivoMes,
80,
IF ( MIC && ObjetivoQ, 240, IF ( MIC && ObjetivoFY, 960, 960 ) )
)
)
)
)
)
)
)
Then create a measure based one the measure.
_contract_ = var _b = SUMMARIZE('end date','end date'[sales Reo],"aaa",'end date'[Objetivos de visita])
return
IF(HASONEVALUE('end date'[sales Reo]),[Objetivos de visita],SUMX(_b,[aaa]))
More details about the error: Dealing with Measure Totals
If it still does not help, please provide your pbix file without privacy information and desired output.
Best regards.
Hi @MQUINT-07 ,
Please have a try.
Objetivos de Visita =
VAR IMT =
SELECTEDVALUE ( BU[BG Fix] ) = "IQS"
RETURN
VAR MED =
SELECTEDVALUE ( BU[BG Fix] ) = "MED"
RETURN
VAR MIC =
SELECTEDVALUE ( BU[BG Fix] ) = "RMS"
RETURN
VAR ObjetivoMes =
HASONEFILTER ( 'End_Date'[FiscalYear] )
&& HASONEFILTER ( End_Date[FiscalQuarter] )
&& HASONEFILTER ( End_Date[MonthName] )
RETURN
VAR ObjetivoQ =
HASONEFILTER ( 'End_Date'[FiscalYear] )
&& HASONEFILTER ( End_Date[FiscalQuarter] )
RETURN
VAR ObjetivoFY =
HASONEFILTER ( 'End_Date'[FiscalYear] )
RETURN
IF (
IMT && ObjetivoMes,
32,
IF (
IMT && ObjetivoQ,
96,
IF (
IMT && ObjetivoFY,
384,
IF (
MED && ObjetivoMes,
80,
IF (
MED && ObjetivoQ,
240,
IF (
MED && ObjetivoFY,
960,
IF (
MIC && ObjetivoMes,
80,
IF ( MIC && ObjetivoQ, 240, IF ( MIC && ObjetivoFY, 960, 960 ) )
)
)
)
)
)
)
)
Then create a measure based one the measure.
_contract_ = var _b = SUMMARIZE('end date','end date'[sales Reo],"aaa",'end date'[Objetivos de visita])
return
IF(HASONEVALUE('end date'[sales Reo]),[Objetivos de visita],SUMX(_b,[aaa]))
More details about the error: Dealing with Measure Totals
If it still does not help, please provide your pbix file without privacy information and desired output.
Best regards.
Hey,
You can calculate the amount dynamically per period by determining a fraction relative to the entire year. Here too you first determine the selected 'BG'. Then you define the annual targets per 'BG'. Then determine which year target is currently selected. I assume your date table contains dates. (If not, let me know, there are other methods to determine the number of days.) Determine the number of days in the current filter context. Now you can dynamically calculate the target based on the share compared to the annual amount. This is the code:
Objetivos de Visita =
VAR IMT =
SELECTEDVALUE ( BU[BG Fix] ) = "IQS"
VAR MED =
SELECTEDVALUE ( BU[BG Fix] ) = "MED"
VAR MIC =
SELECTEDVALUE ( BU[BG Fix] ) = "RMS"
VAR IMT_Year_Target = 384
VAR MED_Year_Target = 960
VAR MIC_Year_Target = 960
VAR Current_Year_Target =
SWITCH (
TRUE,
IMT, IMT_Year_Target,
MED, MED_Year_Target,
MIC, MIC_Year_Target
)
VAR NumberOfDays =
COUNT ( End_Date[Date] )
RETURN
DIVIDE ( Current_Year_Target * NumberOfDays, 365 )
Two other tips: 1. you don't have to write 'RETURN' after every 'VAR' and 2. if you have an extended 'IF' you can change it with 'SWITCH', this is easier to read. Hope this helps.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
24 | |
20 | |
15 | |
12 |
User | Count |
---|---|
55 | |
48 | |
43 | |
19 | |
18 |