Dotted Forecast line for line chart in a cumulative line and a column line

[sv00001]

Hi I have a requirement to show actuals and forcast in a line chart.

the data have 4 categories as below

1. Actuals
2. Planned Actuals
3. Forecast
4. Planned Forecast

I need a cumulative line for actuals Data so I have implemented the below measure for the cumulative line

M=CALCULATE(SUM(Table[value]),FILTER(ALL(Table),Table[Date] <= MAX(Table[Date]) && Table[Type]="Actual")).

all the 4 categories values are in single column with another column conditionally created to classify the type.

solution needed:

I want the planned Actuals from the cumulative line to be dotted from the current date where it is a measure 

I want the planned Forecast from the Values column to be dotted from the current date where it is a column.

can anyone help me with this.

[Ashish_Mathur]

Hi,

The ideal approach should be as follows:

1. Create a Calendar Table with calculated column formulas for Year, Month name and Month number
2. Sort the Month name column by the Month number
3. Create a relationship (Many to One and Single) from the Date column of your Data Table to the Date column of the Calendar Table
4. To your visual, drag the Month name column from the Calendar Table
5. Write this measure

M=CALCULATE(SUM(Table[value]),DATESYTD(Calendar[Date],"31/12"),Table[Type]="Actual")

I have asumed that you want a YTD running total.

[Anonymous · Accepted solution]

Hi @sv00001 ,

Please try:

Planned Actuals = IF(MAX('Table'[Date])<=TODAY(),CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),'Table'[Date]<=MAX('Table'[Date]) && 'Table'[Type]="Planned Actual")))

Planned Forecast = IF(MAX('Table'[Date])>=TODAY(),CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),'Table'[Date]<=MAX('Table'[Date]) && 'Table'[Type]="Planned Forecast")))

Or:

Planned Forecast 2 = 
SWITCH(
    TRUE(),
    MAX('Table'[Date])=TODAY(),[Planned Actuals],
    MAX('Table'[Date])>TODAY(),[Planned Forecast]
)

Output:

Please check the pbix file.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

[sv00001]

IF I use more than 2 && in th measure i dont see any values in the graph

[123abc]

If you're experiencing issues with your DAX measure not displaying values in the graph when using multiple conditions with && (logical AND operator), there could be a few reasons why this might be happening:

1. Data Filtering Issue:

• Ensure that your data model has the appropriate relationships established.
• Check if the data values meet all the conditions you've specified in the DAX measure.

2. Data Range:

• Verify that your data range contains values that satisfy all the conditions specified in the measure.

3. Syntax Error:

• Check for any syntax errors in your DAX measure. Even a small error can prevent the measure from returning expected results.

4. Use of CALCULATE Function:

• Sometimes, the use of CALCULATE with ALL function might interact unexpectedly with other filters applied to the visual. This could result in blank values if the data does not meet the conditions specified in the measure.

5. Context Transition:

• Ensure that the measure is correctly handling context transition. In complex models, the context transition can affect the evaluation of measures.

Example Adjustment:

Let's say your measure looks like this:

Planned_Actuals =
CALCULATE(
SUM(Table[value]),
FILTER(
ALL(Table),
Table[Date] <= MAX(Table[Date]) &&
Table[Type] = "Planned Actual" &&
Table[SomeOtherColumn] = "SomeCondition"
)
)

Ensure that:

• Data exists where Type = "Planned Actual" and SomeOtherColumn = "SomeCondition".
• The date range is suitable for the visualization.

If you're still experiencing issues, you might need to provide more context about your data model, sample data, and the specific DAX measures you're using for further assistance in troubleshooting. Additionally, reviewing the Power BI diagnostic logs or DAX Studio can provide insights into the evaluation of your measures.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[123abc]

To achieve the requirement of having dotted lines for "Planned Actuals" from the cumulative line and "Planned Forecast" from the values column starting from the current date in Power BI, you can follow these steps:

1. Create Calculated Columns:

   • Create a calculated column to classify the type of data (e.g., Actuals, Planned Actuals, Forecast, Planned Forecast).
   • Ensure that your data model has a column that indicates the date.

2. Create Measures:

   • Create a measure for cumulative actuals using DAX, as you've already done.
   • Create measures for cumulative planned actuals and cumulative planned forecast, similar to your cumulative actuals measure, but with appropriate filters for planned actuals and planned forecast data.

3. Line Chart Visualization:

   • Create a line chart visualization.
   • Add your date column to the Axis field well.
   • Add your measures to the Values field well.

4. Formatting Dotted Lines:

   • Select the line chart visualization.
   • Navigate to the "Format" pane.
   • Expand the "Data colors" section.
   • Click on the color options for the lines you want to format.
   • Choose a dotted line style from the options provided.

5. Conditional Dotted Lines:

   • Unfortunately, Power BI doesn't directly support conditional formatting for line styles (such as dotted lines) based on measures or specific conditions.
   • You may need to explore alternatives like creating separate line chart visualizations for different scenarios or using calculated columns to control line styles based on certain conditions, though these methods might not provide the exact solution you're looking for.

6. Dynamic Dotted Lines from Current Date:

   • To make lines dotted from the current date, you may need to adjust your DAX measures to dynamically calculate from the current date onwards.
   • You can use DAX functions like TODAY() or NOW() to get the current date and use it in your measures to filter data accordingly.

Here's an example of how you might adjust your measures to start from the current date onwards:

Cumulative Planned Actuals =
CALCULATE(
SUM(Table[value]),
FILTER(
ALL(Table),
Table[Date] <= MAX(Table[Date]) &&
Table[Type] = "Planned Actual" &&
Table[Date] >= TODAY() // Adjust as necessary
)
)

Similarly, adjust other measures accordingly.

By following these steps, you should be able to create a line chart with cumulative lines for actuals, planned actuals, forecast, and planned forecast, and format them as per your requirements, though achieving dynamic dotted lines from the current date might require additional considerations or workarounds.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.