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.
I have two tables one is calculated by DAX “Monthly_Rate” and the second one is “Monthly_Target”
Daily_Rate
Date | Rates |
01-Dec-2018 | 1300 |
15-Dec-2018 | 1500 |
31-Dec-2018 | 1200 |
01-Jan-2019 | 1900 |
02-Jan-2019 | 1700 |
03-Jan-2019 | 2000 |
04-Jan-2019 | 3000 |
Monthly_Rate Table
Monthly_Rate= SUMMARIZECOLUMNS(Daily_Rate [Date],"Month_Rates",SUM(Daily_Rate [Rates]))
Date | Month_Rates |
Dec-18 | 4000 |
Jan-19 | 8600 |
Monthly_Target Table
Year | Month | Target |
2018 | Dec | 1310 |
2019 | Jan | 1310 |
I am interested in writing a DAX code to calculate the percentage achieved between the Monthly Rates and Target
Something like
% achieved= (Monthly_Rate [Month_Rates])- Monthly_Target [Target]) / Monthly_Target [Target] *100
Thank you
Solved! Go to Solution.
Hi @2019,
To what I can understand from your model theres no need to make a new table with the summarization of the months, do the following:
Date.EndOfMonth (#date([Year], if [Month]= "Jan" then 1 else if [Month]= "Feb" then 2 else if [Month]= "Mar" then 3 else if [Month]= "Apr" then 4 else if [Month]= "May" then 5 else if [Month]= "Jun" then 6 else if [Month]= "Jul" then 7 else if [Month]= "Aug" then 8 else if [Month]= "Sep" then 9 else if [Month]= "Oct" then 10 else if [Month]= "Nov" then 11 else 12 ,1))
This can also be replace by a dax formula:
Month_End_Date =
EOMONTH (
DATE ( 'Montlhy Target'[Year]; SWITCH (
'Montlhy Target'[Month];
"Jan"; 1;
"Feb"; 2;
"Mar"; 3;
"Apr"; 4;
"May"; 5;
"Jun"; 6;
"Jul"; 7;
"Aug"; 8;
"Sep"; 9;
"Oct"; 10;
"Nov"; 11;
12
); 1 );
0
)
% Achieved = VAR Month_Rates = SUM ( Daily_Rate[Rates] ) VAR Target_Rates = SUM ( 'Montlhy Target'[Target] ) RETURN ( Month_Rates - Target_Rates ) / Month_Rates
See result below and attach PBIX file with data model.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @2019,
To what I can understand from your model theres no need to make a new table with the summarization of the months, do the following:
Date.EndOfMonth (#date([Year], if [Month]= "Jan" then 1 else if [Month]= "Feb" then 2 else if [Month]= "Mar" then 3 else if [Month]= "Apr" then 4 else if [Month]= "May" then 5 else if [Month]= "Jun" then 6 else if [Month]= "Jul" then 7 else if [Month]= "Aug" then 8 else if [Month]= "Sep" then 9 else if [Month]= "Oct" then 10 else if [Month]= "Nov" then 11 else 12 ,1))
This can also be replace by a dax formula:
Month_End_Date =
EOMONTH (
DATE ( 'Montlhy Target'[Year]; SWITCH (
'Montlhy Target'[Month];
"Jan"; 1;
"Feb"; 2;
"Mar"; 3;
"Apr"; 4;
"May"; 5;
"Jun"; 6;
"Jul"; 7;
"Aug"; 8;
"Sep"; 9;
"Oct"; 10;
"Nov"; 11;
12
); 1 );
0
)
% Achieved = VAR Month_Rates = SUM ( Daily_Rate[Rates] ) VAR Target_Rates = SUM ( 'Montlhy Target'[Target] ) RETURN ( Month_Rates - Target_Rates ) / Month_Rates
See result below and attach PBIX file with data model.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering 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 |
---|---|
111 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |