Show null values as "0" in line chart for last 4 weeks data

[Bilal_Anwar]

Hi,

I need help in fixing the line chart. i want to indicate the values in last 4 weeks including if the value is zero as well.

here is the issue brief.

i have 3 tables.

1- FACT Table - tb_data 

2- DATES Table - Tb_dimdate

3- Dummy Date Table - dummydates (which is copy of tb_date and is being used to get x-axis range for last 2 weeks based on Max(Tb_date)

FACT Table and DATES Table are linked.

i have created measure to calculate the data in last 4 weeks. here is the DAX expression.

Last 4 weeks Data =
VAR _maxdate = MAX(tb_DimDate[Date])
VAR _lastinperiod =
DATESINPERIOD(DummyDates[Date],_maxdate,-28,day)
RETURN

CALCULATE(
SUM(Tb_Data[Value]),
REMOVEFILTERS(tb_DimDate[Date]),
KEEPFILTERS(_lastinperiod),
USERELATIONSHIP(tb_DimDate[Date],DummyDates[Date])
)

Tables have following relationship

the line chart and matrix visual show only dates which have values > 0. i want to include even "0" values.

i have tried both alternate solutions.

1- show items with no data (x-axis)

2- calculate( if(ISBLANK(sum() , 0 , sum() )

and both are not working.

In both cases the x-axis range changes to complete dates table.

Requirement: i need data for 4 weeks including 0 values in line chart.

[Anonymous · Accepted solution]

Hi @Bilal_Anwar ,

You can follow the steps below to get it:

1. Please delete the relationship between Tb_Data table and DummyDates table

2. Update the formula of measure [Last 4 weeks Data] as below if you applied the date field of DummyDates table on X axis field of line chart

Last 4 weeks Data =
VAR _maxdate =
    MAX ( tb_DimDate[Date] )
VAR _seldate =
    SELECTEDVALUE ( DummyDates[Date] )
VAR _lastinperiod =
    DATESINPERIOD ( DummyDates[Date], _maxdate, -28, DAY )
RETURN
    IF (
        _seldate IN _lastinperiod,
        CALCULATE (
            SUM ( Tb_Data[Value] ),
            REMOVEFILTERS ( tb_DimDate[Date] ),
            KEEPFILTERS ( _lastinperiod ),
            FILTER ( tb_DimDate, tb_DimDate[Date] = _seldate )
        ) + 0,
        BLANK ()
    )

If the above one can't help you get the expected result, please provide some raw data in your table 'Tb_Data' (exclude sensitive data) with Text format and your visual settings. It would be helpful to find out the solution. You can refer the following link to share the required info:

How to provide sample data in the Power BI Forum

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[Bilal_Anwar]

Thank you @Anonymous for detailed reponse. i get what you have recommended and definitely it will work as well. 

i looked at my data tables and found out that during unpivoting the data column i was replacing the "null" values with "0" and later after unpivoting i was replacing it back to null values. which was resulting in null values and ultimately wasnt working with my formula for dates in period. now i have got it sorted. i will use your method and test it. DAX looks ok to me, should work.