Calculate percentage for two columns in two separated tables

[2019]

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

[MFelix · Accepted 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:

• On Target Table create a new column in the Query editor for Month_End_Date

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
)

• Create a Calendar Table and make one to many relationship:
  • Calendar[Date] - > Daily Rates [Date]
  • Calendar[Date] - > Montlhy Target[Month_End_Date]
• Create the following measure:

% 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