Forum Discussion
Helper IQuarter calculation upto selected month
First make sure that your date table has a date column which uniquely identifies the year and month. In my example I am using 'Date'[Start of month], but you could equally use end of the month. The key point is that it is of type date, so that MAX will work correctly.
Create a disconnected copy of the date table. It should not have a relationship to any other tables, it is only for use in the slicer. In my example I call the table 'For Slicer'.
Use 'Date'[Year] and 'For Slicer'[Month name] in separate slicers. In your chart visual, use 'Date'[Quarter].
Create a measure like
My Measure = VAR MonthInSlicer = CALCULATE( MAX( 'For Slicer'[Start Of Month] ), TREATAS( VALUES( 'Date'[Calendar Year Number] ), 'For Slicer'[Calendar Year Number] ) ) VAR DateToUse = CALCULATE( MAX( 'Date'[Start Of Month] ), KEEPFILTERS( 'Date'[Start Of Month] <= MonthInSlicer ) ) VAR Result = CALCULATE( [Base Measure], 'Date'[Start Of Month] = DateToUse ) RETURN Resultwhere [Base Measure] is whatever you are trying to compute. You could also turn this code into a calculation item, and call SELECTEDMEASURE() instead of [Base Measure].
You need to use a disconnected table or the other quarters will not be v isible. Filters coming from a related table or from the same table will show only the rows that's been selected so if you select August, you will see Q3 only,.
Advocate IThe 'measure' you mention that is not working is actually a calculated column in DAX. It works if you use it as a column definition in your date table.
But I have a hard time understanding your business requirements. I am a but confusiod what your goal is with showing a visual based on Quarters, but then showing only a single month in all quarters. Isn't that just displaying a month-over-month visual?
Helper IWhen I select month as apr and year as 2025 it should appear like this in chart where Q1 has march value and Q2 has apr value
- Kobes
Details matter here. Do you mean 'Q1 up to March' or 'Q1 only March'?
If you mean the last, I would suggest creating a hierarchy with Quarter and Month. Use that hierarchy for the X-axis and expand the selection to show the months, too (or without a hierarchy just add both columns: quarter and month). Then you don't have to code anything in DAX and users immediately see what is happening.
If you want the 'up to' version, I think my earlier suggestions should be useful to you.
If you really want to define the Q classification to mean <<some arbitrary month in the quarter displayed>> you need to be more precise in how 'arbitrary month' is defined in all possible cases to come up with a suggestion that works. But it seems incredibly confusing to me for your users if Q1 in april is not actually Q1, but a single month from Q1.
- danextian
Super User
Hi Harika05
If what you're trying to achieve is show the month of the previous quarter and the currently selected month, please try the attched pbix.
- Harika05
Hello danextian ,
Thanks for the solution ,I have checked the solution(where the filters you are using are coming from island/disconnected table) ,In my case the filters come from the calendar table linked to the main fact table.
I have created a replica of calendar and created a inactive relationship
In chart the I am using the one from disconnecetd table i.e Previous Date
My requirement is like :
If I select month jan , Q1 should be visible only with jan data
If I select month feb, Q1 should be visible only with feb data
If I select month mar , Q1 should be visible only with mar data
If I select month apr , Q1 should be visible only with mar data and Q2 with apr data
If I select month may , Q1 should be visible only with mar data and Q2 with may data
If I select month jun, Q1 should be visible only with mar data and Q2 with jun data
If I select month jul , Q1 should be visible only with mar data and Q2 with jun data and Q3 with jul data
If I select month aug, Q1 should be visible only with mar data and Q2 with jun data and Q3 with aug data
If I select month sep, Q1 should be visible only with mar data and Q2 with jun data and Q3 with sep data
If I select month oct, Q1 should be visible only with mar data and Q2 with jun data and Q3 with sep data and Q4 with oct data
If I select month nov, Q1 should be visible only with mar data and Q2 with jun data and Q3 with sep data and Q4 with nov data
If I select month dec, Q1 should be visible only with mar data and Q2 with jun data and Q3 with sep data and Q4 with dec data
For ex when I select dec this is what my chart should look like
- danextian
Using the same pbix in my initial reply, you can modify the measure a bit to return the latest month of the past quarters based on the currenly selected month
My Measure = VAR _CurrentMonth = MAX ( Months[End of Month] ) VAR _PrevQuarterEnds = FILTER ( ALL ( Dates[End of Month] ), Dates[End of Month] < _CurrentMonth && MONTH ( Dates[End of Month] ) IN { 3, 6, 9, 12 } ) RETURN CALCULATE ( [Total Revenue], TREATAS ( UNION ( ROW ( "End of Month", _CurrentMonth ), SELECTCOLUMNS ( _PrevQuarterEnds, "End of Month", Dates[End of Month] ) ), Dates[End of Month] ) )