Forum Discussion
Quarter calculation upto selected month
- 4 months ago
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].
- 4 months ago
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,.
When 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
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.