Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
CS
Frequent 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
CS
Frequent Visitor

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.

View solution in original post

1 REPLY 1
CS
Frequent Visitor

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.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.