Dynamic Slicer Quarter, Month

Coffee1234 — Fri, 26 Jan 2024 18:18:57 GMT

Hi,

I am building a dashboard which shows goals for each month. My goals table looks something like this

Store Number	Month	Goal	Quarter
1	January	$10	1
2	January	$20	1
3	January	$30	1
1	February	$20	1
2	February	$30	1
3	February	$40	1
1	March	$30	1
2	March	$40	1
3	March	$50	1
1	April	$40	2
2	April	$50	2
3	April	$60	2
1	May	$50	2
2	May	$60	2
3	May	$70	2
1	June	$60	2
2	June	$70	2
3	June	$80	2
1	July	$70	3
2	July	$80	3
3	July	$90	3
1	August	$100	3
2	August	$90	3
3	August	$100	3
1	September	$110	3
2	September	$100	3
3	September	$110	3
1	October	$120	4
2	October	$110	4
3	October	$120	4
1	November	$130	4
2	November	$120	4
3	November	$130	4
1	December	$140	4
2	December	$150	4
3	December	$160	4

I currntly have a slicer by Month name. The DAX to return the Goal for the selected month is

Goal =
CALCULATE (
SUM ('Goal table'[goal]),
FILTER (
ALLSELECTED ('Dates'), [Date]
IN FILTERS ('Dates'[Date]))

What I would like to do is be able to have a slicer which shows an Option to select the Quarter OR the Month Name and have the report update the goals based on the slicer. For example, if the report user selects Quarter 1 in the slicer, the report would look something like this:

Store	Goal
1	$60
2	$90
3	$120

But if the report user selects the month of Juy for example, then the report would look something like this:

Store	Goal
1	$70
2	$80
3	$90

I have done some research on Field Formatting, which sounds like what I would need. However, my understanding is that I need to have PBI on the cloud, which I do not and cannot get at this time.

Is there a work around to produce something above?

Thank you so much!!

Re: Dynamic Slicer Quarter, Month

lbendlin — Sat, 27 Jan 2024 02:40:45 GMT

Use a proper calendar table with Date, Month, Quarter and Year columns. Add Year, Quarter and Month to a slicer , which will create a hierarchy for you. Or use the built-in date hierarchies (not recommended).

Re: Dynamic Slicer Quarter, Month

ThxAlot — Sat, 27 Jan 2024 09:04:28 GMT

Construct a proper mapping table for the slicer,

topic Re: Dynamic Slicer Quarter, Month in DAX Commands and Tips

Dynamic Slicer Quarter, Month

Re: Dynamic Slicer Quarter, Month

Re: Dynamic Slicer Quarter, Month