Include blank date data when filter by date slicer

[ravitejaballa]

I am trying to show all data with values as zero even we don't have data on a given date.

Here is sample data.
I have signalValue data only for AssetID A2.

But when the user selects any date. I need to show all assets A1, A2, A3.

If we don't have data on a given date. we should show zero/blank.

In this case, A1 and A3 don't have data between 31st Oct to 7th Nov.

using measure I am able to do. But it will work only if FROM date stay at 31st Oct.

if we select any date from 1st Nov in FROM, Measure fails.
The reason is we won't get a blank datestamp when we select FROM and TO date (we can see this by running table query in DAX Studio).

I am not sure how to fix it.

AssetID

OrgID

isactive

SignalOrgID

SignalValue

SignalDateStamp

Belongs

SignalAssetID

A1

O1

False

No

A1

O2

True

Yes

A3

O3

True

Yes

A2

O1

False

O2

0.5

31-Oct-21

No

A2

A2

O2

True

O2

0.5

31-Oct-21

Yes

A2

A2

O2

True

O2

0

1-Nov-21

Yes

A2

A2

O1

False

O2

0

1-Nov-21

No

A2

A2

O2

True

O2

2.291944742

2-Nov-21

Yes

A2

A2

O1

False

O2

2.291944742

2-Nov-21

No

A2

A2

O1

False

O2

0.752222061

3-Nov-21

No

A2

A2

O2

True

O2

0.752222061

3-Nov-21

Yes

A2

A2

O2

True

O2

0.526666641

4-Nov-21

Yes

A2

A2

O1

False

O2

0.526666641

4-Nov-21

No

A2

A2

O2

True

O1

2.1

5-Nov-21

No

A2

A2

O1

False

O1

2.1

5-Nov-21

Yes

A2

A2

O2

True

O1

0.45

6-Nov-21

No

A2

A2

O1

False

O1

0.45

6-Nov-21

Yes

A2

A2

O2

True

O2

0.5

7-Nov-21

Yes

A2

A2

O1

False

O2

0.5

7-Nov-21

No

A2

I created a measure(SignalValue_All_Assets) to show a zero when we don't have data on a given date.
I have data from 31st Oct to 7th Nov

[v-yangliu-msft · Accepted solution]

Hi  @ravitejaballa ,

Here are the steps you can follow：

1. Create a calendar table.

Table 2 = CALENDAR(DATE(2021,10,31),DATE(2021,11,7))

2. Create measure.

Measure =
SUMX(FILTER('MySampleData',
'MySampleData'[SignalDateStamp]>=MIN('Table 2'[Date])&&'MySampleData'[SignalDateStamp]<=MAX('Table 2'[Date])
),[SignalValue])

3. Use [Date] of the calendar table as the slicer

4. Result:

When the date is displayed from November 1 to November 7:

When the date is displayed as November 5th:

Best Regards,

Liu Yang

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

[ravitejaballa]

@v-yangliu-msft  Thank you

Add on to your steps (for future reference to others)

-> On column, make 'Show items with no data'

[ravitejaballa]

@DataZoe  
Yes, the above solution shows all assets.

However, it shows the incorrect mapping.

Example.
on 5th Nov we have data only for AssetID(A2) - OrgID(O1)
But other assets (A1, A3) is not mapped under O1.

A1 should show O2

A3 should show O3

[DataZoe]

@ravitejaballa I was able to get it to do this by creating a lookup/dimension table for AssetID, then using that instead in the matrix:

1. Create a new table in "Data Modeling" ribbon -> "New Table":

Assets = SUMMARIZE('Table','Table'[AssetID])

2. Create relationship between Assets and the main table on AssetID:

Now when I filter the dates, the others still show: