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

[Dickkieee]

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

[Anonymous · Accepted solution]

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.