cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular Visitor

## How to modify my DAX to see weeks with no revenue.

I'm trying to visualize the total revenue per week, include the weeks with no revenue. For this purpose I tried to use

CALCULATE(
SUM('fct_test'[revenue]) )

Giving me the following result

But as you can see this visual is without the null weeks.

Therefore I tried it with the following DAX

CALCULATE(
SUM('fct_test'[revenue]),
FILTER(
ALL('DimDate'[Date]),
'DimDate'[Date] <= MAX('DimDate'[Date])

The blue line is a target line (linear).

How can I make a combination of both with the blue line and the first visualisation.

For the blue line I use the following dax

VAR b = [# CapaciteitHallen]
VAR a = 0.18
VAR pipestart = TODAY()
VAR maxdate = MAX(DimDate[Datum])
VAR x = datediff(pipestart,maxdate,week)
VAR line = b + a*b*x
RETURN IF(pipestart <= maxdate, line, BLANK())

Hope someone can help
1 ACCEPTED SOLUTION
Community Support

Hi @Dickkieee ,

To ensure that weeks with no revenue are included in your visual, you need to make sure that your date table (DimDate) has a continuous range of dates and that it is not filtered out by the relationships or the context of the report. Here's a revised version of your DAX measure that should help:

``````Total Revenue with Empty Weeks =
CALCULATE (
SUM ( 'fct_test'[revenue] ),
ALL ( 'DimDate' ),
// This removes any filters from the DimDate table
VALUES ( 'DimDate'[WeekNumber] ) // This ensures that you still group by week
)
``````

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support

Hi @Dickkieee ,

To ensure that weeks with no revenue are included in your visual, you need to make sure that your date table (DimDate) has a continuous range of dates and that it is not filtered out by the relationships or the context of the report. Here's a revised version of your DAX measure that should help:

``````Total Revenue with Empty Weeks =
CALCULATE (
SUM ( 'fct_test'[revenue] ),
ALL ( 'DimDate' ),
// This removes any filters from the DimDate table
VALUES ( 'DimDate'[WeekNumber] ) // This ensures that you still group by week
)
``````

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors