Forum Discussion

Harika05's avatar
Harika05
Icon for Helper I rankHelper I
4 months ago
Solved

Quarter calculation upto selected month

Hello, I want a bar chart to reflect for Quarter in this way. If I select jan in Q1 jan should appear ,feb selected - feb value should appear in Q1 ,mar selected then mar value should be there in Q...
  • johnt75's avatar
    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 Result

    where [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].

  • danextian's avatar
    danextian
    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,.