Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Dear Community,
I have a line chart like this:
It shows how many orders we placed every month of every year. It just what I want, but in June 2022 we didnt place any orders.
As you can see the line stops but I want that the line will go to zero.
Maybe I need a dax code for this,
I hope somebody can help me.
To ensure that the line chart in Power BI shows zero values for months where no orders were placed, you can create a DAX measure to handle this scenario. You can use DAX to fill in the missing data points with zero values. Here's how you can do it:
Create a Date Table: If you haven't already created a date table, create one with a continuous sequence of dates covering your entire date range.
Create a Measure: Create a measure that calculates the number of orders placed for each month. Let's call this measure "Orders Placed".
Fill in Missing Data: Use DAX functions to fill in the missing data points with zero values. You can use the COALESCE and SUMX functions to achieve this.
Here's a sample DAX code for your measure:
Orders Placed with Zeroes =
VAR OrdersByMonth =
SUMMARIZE(
'Date',
'Date'[Year],
'Date'[Month Number],
"Total Orders", [Orders Placed]
)
VAR AllMonths =
ADDCOLUMNS(
GENERATE(
VALUES('Date'[Year]),
VALUES('Date'[Month Number])
),
"Year", 'Date'[Year],
"Month Number", 'Date'[Month Number]
)
RETURN
COALESCE(
SUMX(
AllMonths,
IF(
COUNTROWS(
FILTER(
OrdersByMonth,
'Date'[Year] = [Year] && 'Date'[Month Number] = [Month Number]
)
) > 0,
[Total Orders],
0
)
),
0
)
This DAX measure fills in the missing data points with zeroes for each month of each year. It first calculates the total orders placed for each month, then iterates over all months and checks if there are orders placed for that month. If orders are found, it returns the total orders; otherwise, it returns zero.
Replace 'Date' with the name of your date table and 'Orders Placed' with the name of your existing measure that calculates the number of orders placed.
Once you create this measure, use it in your line chart instead of the previous measure. The line chart will now display zero values for months where no orders were placed.
If I answered your question, please mark my post as solution, Appreciate your Kudos.
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
33 | |
16 | |
13 | |
10 | |
8 |
User | Count |
---|---|
59 | |
20 | |
12 | |
11 | |
10 |