topic Date with few empty dates - hwo to create Between slicer in DAX Commands and Tips

Date with few empty dates - hwo to create Between slicer

annam — Thu, 18 Jul 2024 04:45:34 GMT

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?

Re: Date with few empty dates - hwo to create Between slicer

Joe_Barry — Thu, 18 Jul 2024 06:07:42 GMT

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

Re: Date with few empty dates - hwo to create Between slicer

Anonymous — Fri, 19 Jul 2024 08:31:39 GMT

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.

Here are the columns included in my example table.
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())

)
Create a one-to-many relationship from table“Payment” to table“CompleteTable” by “Date” columns.
Put “Date”, “Amount” from table“CompleteTable” and “Name” from table“Payment” into the visual object. Put “Date” from table“CompleteTable” into the slicer.
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.}