Menu Close

Combining ACTUALS and FORECAST as one LINE in Power BI using Dynamics CRM Sales Data

First you will need to have Dynamics 365 for sales.  Next you will want to have Power BI Desktop installed on your machine.  Once you have that installed open Power BI Desktop.  When it ask you for data you will want to search for “Dataverse” You will then connect to the Dataverse with the login info you use for Dynamics 365.  Pull in the Opportunity table into Power BI.

              Next you will create a date table in Power BI called dimdate.  You will go up to the ribbon bar and select get data – > blank query.  You will create two parameters, one called StartYear and the other EndYear.   StartYear will be a data type of decimal, and value 2000.  We will create the EndYear and the data type of decimal and value of 2030.  You will then go to the dimDate table you are creating.  You will go to the advanced Editor, and paste in the code below.

let
    StartDate = #date(StartYear,1,1),
    EndDate = #date(EndYear,12,31),
    NumberOfDays = Duration.Days( EndDate - StartDate ),
    Dates = List.Dates(StartDate, NumberOfDays+1, #duration(1,0,0,0)),
    #"Converted to Table" = Table.FromList(Dates, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "FullDateAlternateKey"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"FullDateAlternateKey", type date}}),
    #"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([FullDateAlternateKey]), type number),
    #"Inserted Month" = Table.AddColumn(#"Inserted Year", "Month", each Date.Month([FullDateAlternateKey]), type number),
    #"Inserted Month Name" = Table.AddColumn(#"Inserted Month", "Month Name", each Date.MonthName([FullDateAlternateKey]), type text),
    #"Inserted Quarter" = Table.AddColumn(#"Inserted Month Name", "Quarter", each Date.QuarterOfYear([FullDateAlternateKey]), type number),
    #"Inserted Week of Year" = Table.AddColumn(#"Inserted Quarter", "Week of Year", each Date.WeekOfYear([FullDateAlternateKey]), type number),
    #"Inserted Week of Month" = Table.AddColumn(#"Inserted Week of Year", "Week of Month", each Date.WeekOfMonth([FullDateAlternateKey]), type number),
    #"Inserted Day" = Table.AddColumn(#"Inserted Week of Month", "Day", each Date.Day([FullDateAlternateKey]), type number),
    #"Inserted Day of Week" = Table.AddColumn(#"Inserted Day", "Day of Week", each Date.DayOfWeek([FullDateAlternateKey]), type number),
    #"Inserted Day of Year" = Table.AddColumn(#"Inserted Day of Week", "Day of Year", each Date.DayOfYear([FullDateAlternateKey]), type number),
    #"Inserted Day Name" = Table.AddColumn(#"Inserted Day of Year", "Day Name", each Date.DayOfWeekName([FullDateAlternateKey]), type text)
in
    #"Inserted Day Name"

Next you will go to the opportunity table and create a couple of fields.  The first one will be a combined date field that will grab the Estimated closed date if there is not an actual close date.  We will do this by creating a custom field called CommbinedDate = if(opportunity[actualclosedate] = BLANK(), opportunity[estimatedclosedate],opportunity[actualclosedate])

You will then create a relationship from the opportunity table to the dimDate table.  The relationship will be the opportunity CombinedDate field we just created and the FullDateAlternateKey  On the FullDateAlternateKey you will want to make sure that its datatype datetime format, and you select the top format.

Next you will create a couple more fields on the opportunity table. First we will create a new measure called. Sales Actuals = sum(opportunity[actualvalue])

Next we will create another measure called Sales Forecast = sum(opportunity[estimatedvalue])

And we will create the last measure called Sales and forecast, that will do all the work

 Sales and Forecast =

VAR LastSalesDate =

CALCULATE(

    Max(opportunity[actualclosedate]),

    REMOVEFILTERS()

)

VAR Actuals = [Sales Actuals]

VAR FutureForecast =

CALCULATE(

    [Sales Forecast],

    FILTER(dimDate,dimDate[FullDateAlternateKey] >= LastSalesDate)

    )

VAR Result = FutureForecast + Actuals

RETURN

Result

Now you can create a line graph.

You will grab from the dimDate table the FullDateAlternateKey  as the axis and for the values you want to make sure you have them in this order.  The Sales and Forecast on Top and then the Sales Actuals  You will then have a Line chart of the sales actuals and forecast on the same line.

Tagged ,