Line chart Power BI - different value for different period of time

user8rLl1 0
Viewed 41

I am using power BI and I would like to create a line chart which contains values from two tables (sales history and sales prediction). So for the past 12 months, the line should reprensent the sales history and for the next 6 months, the line reprensents sales prediction. Here is what the data looks like, lets say we are in June 2021:

I know there is a way to do it in DAX but I don't know how to do it by myself. Thanks a lot in advance for your help !

Calendar

Sales Prediction

Result

powerbi-custom-visuals linechart dax powerbi
2 Answers

You can achieve this by

  1. Delete any relationship between Calendar and the other two tables if there is any, as we are going to use DATESBETWEEN function to calculate

  2. Create two metric like below, you might need to adjust the column names as per your project

Sales History Amount = IF('CALENDAR'[Date] <= TODAY(), CALCULATE(SUM('Sales History'[Amount]), DATESBETWEEN('Sales History'[Date], MIN('CALENDAR'[Date] ), MAX('CALENDAR'[Date]))), BLANK())

Sales Prediction Amount = IF('CALENDAR'[Date] >= TODAY(), CALCULATE(SUM('Sales Prediction'[Amount]), DATESBETWEEN('Sales Prediction'[Date], MIN('CALENDAR'[Date] ), MAX('CALENDAR'[Date]))), BLANK())

  1. Add these two metrics in the table and use the Date from the Calendar table as X axis.

  2. Format the first metric to solid line, and dash line for the second

Penn0

Calendar should be linked to your tables.

Prediction Amount =
VAR lastSalesDate = MAX('Sales History'[Date])
VAR currentPredictionAmnt = 
        CALCULATE(
            SUM('Sales Prediction'[Amount])
            ,KEEPFILTERS('CALENDAR'[Date]>lastSalesDate)
        )    
RETURN currentPredictionAmnt + SUM()


Sales Amount = 
SUM('Sales History'[Amount])
userLOTh10
Related