Date with few empty dates - hwo to create Between slicer

[annam]

I have payment date column and trying to create slicer with between. Bu when I select date from slicer it empties table. any work around?

[Anonymous · Accepted solution]

Hi @annam 

My solution is to add missing dates in the source data and fill in the amount as “BLANK”.

In this way, when using a slicer with between, there will be no interval dates that cause visual objects to be empty.

1. Here are the columns included in my example table.  
2. Create a new table using the following DAX :

   CompleteTable = ADDCOLUMNS(

       CROSSJOIN(

           CALENDAR(MIN(Payment[Date]), MAX(Payment[Date])),

           DISTINCT(Payment[Name])),

           "Amount",

           COALESCE(LOOKUPVALUE(Payment[Amount],Payment[Date],[Date]), BLANK())

   ) 

3.  Create a one-to-many relationship from table“Payment” to table“CompleteTable” by “Date” columns.  

4. Put “Date”, “Amount” from table“CompleteTable” and “Name” from table“Payment” into the visual object. Put “Date” from table“CompleteTable” into the slicer.  

5. Here is my final test result.  

I hope this solution meets your requirements.

^{Best Regards,}

^{Jarvis Tang}

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

[Joe_Barry]

Hi @annam 

Why not use a Date Table?

Create an Active One to Many relationship from the Date column in the Date table to the payment date column in your table.

Create a slicer from the Date column in the date table and use the columns from the Data table in a visual that has a measure from your table.

Hope this helps

Joe