cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
CS Regular Visitor
Regular Visitor

List Month End or Quarter End Dates in a Slicer Based on User Selection in Another Slicer

 I have a parameter table, Date Detail, that is used in a slicer to allow the user to select either the month or the quarter.

 

Date Detail table, no other columns or data:

 

Date Detail table.png

 

Based on the user's selection, I want to display either the end of month or the end of quarter dates in another slicer. The Dates table has columns for both of these dates.

 

Dates table has EOM and EOQ columns along with other date columns:

 

Dates Table EoM and EoQ Columns.png

 

How can I populate a slicer with either the End of Month or End of Quarter dates based on the selection of month or quarter in the Date Detail-Time Period slicer?

 

Should I add the end of month and end of quarter dates to the Date Detail parameter table? Is there a way to do this with DAX, if so? I would rather have an auto-populating solution based on "code" than typing it in manually if adding these dates to the parameter table is the way to go. Or is there another solution altogether?

1 ACCEPTED SOLUTION

Accepted Solutions
CS Regular Visitor
Regular Visitor

Re: List Month End or Quarter End Dates in a Slicer Based on User Selection in Another Slicer

I solved this myself and am putting what I did here in case it will help someone else. Also, I would be interested in any type of crossjoin or other solutions still.

 

  • In Power Query, I made two reference queries to the date table and deleted all columns but the End of Month column from one query and the End of Quarter column from the other. 
  • I added a "Time Period" column with the value of "Month" to the End of Month query/table and a "Time Period" column with the value of "Quarter" to the End of Quarter query/table.
  • I deduped the dates in each table and changed the date column heading to "Date" in each table so they would match in an append.
  • Next, I appended the month and quarter queries into a new table. 

Now I am able to use the values from this table in slicers and the slicers filter each other when I make a selection from one of them.

1 REPLY 1
CS Regular Visitor
Regular Visitor

Re: List Month End or Quarter End Dates in a Slicer Based on User Selection in Another Slicer

I solved this myself and am putting what I did here in case it will help someone else. Also, I would be interested in any type of crossjoin or other solutions still.

 

  • In Power Query, I made two reference queries to the date table and deleted all columns but the End of Month column from one query and the End of Quarter column from the other. 
  • I added a "Time Period" column with the value of "Month" to the End of Month query/table and a "Time Period" column with the value of "Quarter" to the End of Quarter query/table.
  • I deduped the dates in each table and changed the date column heading to "Date" in each table so they would match in an append.
  • Next, I appended the month and quarter queries into a new table. 

Now I am able to use the values from this table in slicers and the slicers filter each other when I make a selection from one of them.