skip to main content
Power BI
    • What is Power BI
    • Why Power BI
    • Customer stories
    • Data visuals
    • Security
    • Power BI Desktop
    • Power BI Pro
    • Power BI Premium
    • Power BI Mobile
    • Power BI Embedded
    • Power BI Report Server
  • Pricing
    • Azure + Power BI
    • Office 365 + Power BI
      • Energy
      • Healthcare
      • Manufacturing
      • Retail
    • For analysts
    • For IT
      • Overview
      • Embedded analytics
      • Power BI visuals
      • Automation
      • Documentation
      • Community
    • Overview
    • Find consulting services
    • Partner showcase
    • Find a partner
    • Become a partner
      • Overview
      • Guided learning
      • Documentation
      • Webinars
      • Roadmap
      • Overview
      • Issues
      • Give feedback
    • Blog
    • Overview
    • Forums
    • Galleries
    • Submit ideas
    • Events
    • User groups
    • Community blog
    • Register
    • ·
    • Sign In
    • ·
    • Help
    Go To
    • Galleries
    • COVID-19 Data Stories Gallery
    • Themes Gallery
    • Data Stories Gallery
    • R Script Showcase
    • Webinars and Video Gallery
    • Quick Measures Gallery
    • MBAS Gallery
    • MBAS Gallery 2020
    cancel
    Turn on suggestions
    Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
    Showing results for 
    Search instead for 
    Did you mean: 
    • Microsoft Power BI Community
    • Galleries
    • Quick Measures Gallery
    • Combine Sales and Forecast

    Combine Sales and Forecast

    05-14-2019 07:46 AM

    Anonymous
    Not applicable
    2029 Views
    LinkedIn LinkedIn Facebook Facebook Twitter Twitter
    Anonymous
    Not applicable
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Email to a Friend
    • Report Inappropriate Content

    Combine Sales and Forecast

    ‎05-14-2019 07:46 AM

    Intro:

    There are many ways to analyze Actual Sales and Forecasts. One commonly used metric is the Year-End forecast, which combines Actual Sales with Forecasts for future dates. 

     

    This example uses monthly values for both Sales and Forecast. Daily data will work with these measures also. 

     

    Date Cutoff:

    A future date is any date which does not have Sales information available. There are two ways to mark the cutoff date:

    1. Create a new column in the date table, which indicates whether or not each date is before or after the cutoff date. In this case, I use an M script for the date dimension. Since the Sales data is at the month level, it finds the most recent month with Sales and then checks whether the month is before or after that month:

     

    maxSales = List.Max(Table.Column(Sales, "yearmonth")),
    InsertHasSalesData = Table.AddColumn(#"Changed Type", "Has Sales Data", 
    each if [Month Key] <= maxSales then "Yes"
    else "No")

    2. Create a new Measure. In this case, we already have the dates marked, so we can use that information:

     

     

    Max Sales Date = 
    CALCULATE(
        MAX('Date'[Date]),
        FILTER(
            ALL('Date'),
            'Date'[Has Sales Data] = "Yes"
        )
    )

    Alternatively, the Sales date could be referenced. (Again, this example uses monthly data - this will not work here):

     

     

    Max Sales Date = 
    CALCULATE(
        MAX('Sales'[Sales Date]),
        ALL('Date')
    )

     

     

    Connecting Actuals and Forecast in Line Charts:

    Using the cutoff date, the Forecast can be limited to only consider later dates. However, to make a continuous line chart, the measure also needs to match the Actuals on the max Actuals date. Monthly and YTD measures can be accomplished as follows:

     

    Amount Forecast Future Only With Final Sales = 
    var curdate = MAX('Date'[Date])
    return SWITCH(
        TRUE(),
        curdate < [Max Sales Date], BLANK(),
        curdate = [Max Sales Date], [Amount Sales],
        [Amount Forecast Future Only]
    )
    Amount Forecast Future Only YTD With Final Sales = 
    var curdate = MAX('Date'[Date])
    return SWITCH(
        TRUE(),
        curdate < [Max Sales Date], BLANK(),
        [Amount Sales YTD] + [Amount Forecast YTD Future Only]
    )

    Cheers!

     

    Nathan

    eyJrIjoiZDJmNTQwZDctODI3ZS00ODg5LWFiYzctODk4NzYzYzIxMTcyIiwidCI6IjM1MDY5ZDc0LTE0ODktNDE5NC04MGM3LTNhODEzODVlYWQ1YiIsImMiOjh9

    Preview file
    11 KB
    Combine Sales and Forecast.pbix
    Preview file
    13 KB
    Labels:
    • Labels:
    • Time Intelligence
    Message 1 of 1
    2,029 Views
    2
    Reply
    • All forum topics
    • Previous Topic
    • Next Topic
    Preview Exit Preview

    never-displayed

    You must be signed in to add attachments

    never-displayed

    Additional options
    You do not have permission to remove this product association.
     

    Power Platform

    • Overview
    • Power BI
    • Power Apps
    • Power Automate
    • Power Virtual Agents

    • Sign in
    • Sign up

    Browse

    • Solutions
    • Partners
    • Consulting Services

    Downloads

    • Power BI Desktop
    • Power BI Mobile
    • Power BI Report Server
    • See all downloads

    Learn

    • Guided learning
    • Documentation
    • Support
    • Community
    • Give feedback
    • Webinars
    • Developers
    • Blog
    • Newsletter

    • © 2021 Microsoft
    • Follow Power BI
    • Privacy & cookies
    • Manage cookies
    • Terms of use
    • Trademarks