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,.
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]
)
)
Thanks for the solution I tried to apply it but its giving same value across all quarters if i select month for ex I selected aug and its giving the same Aug value for Q1,Q2,Q3
And also in above .pbix and formula you are using disconnected/island table year and month as slicers .But in my case I need the Calendar table connected to main fact table to be used as slicers as others chart in table are dependent on it.
- danextian4 months ago
Super User
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,.