Forum Discussion

ravitejaballa's avatar
ravitejaballa
Icon for Helper III rankHelper III
4 years ago
Solved

Include blank date data when filter by date slicer

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 Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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

DataZoe's avatar
DataZoe
Icon for Microsoft Employee rankMicrosoft Employee
4 years ago

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: