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,.
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.
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,.