Forum Discussion

ravitejaballa's avatar
ravitejaballa
Helper 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 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

 

 

 

 

 

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

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    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

    • ravitejaballa's avatar
      ravitejaballa
      Helper III
      4 years ago

      Anonymous  Thank you

       

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

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

       

  • DataZoe's avatar
    DataZoe
    Microsoft 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:

     


     

  • ravitejaballa's avatar
    ravitejaballa
    Helper III
    4 years ago

    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