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,.
Hi Harika, if I understand you well, you are trying to show all months in a quarter that have either started or are fully in the past, but not months from any quarter that are part of the future.
On way you may be able to achieve this is by adding a 'helper' column in the Calendar called "MonthsFromNow', it will contain a 0 for the current month, a negative value for past months and a positive value for the future months. The DAX looks like this for that column:
MonthsFromNow = DATEDIFF(TODAY(), 'Calendar'[%DATE_ID), MONTH )
With this column, you can then do:
Sum of Table[Value] :=
CALCULATE (
SUM (Table[Value] ),
Calendar[MonthsFromNow] <= 0
)
If you want the limit of data to show being related to today, your best friend is likely to be DATESYTD and passing that as a filter table to your calculate reference:
Sum of Table[Value] YTD :=
VAR _DatesYTD = CALCULATETABLE (
DATESYTD( 'Calendar'[%DATE_ID] ),
'Calendar'[%DATE_ID] <= TODAY()
)
RETURN
CALCULATE(
SUM (Table[Value] ),
_DatesYTD
)
Hello Kobes , By default an year (single select slicer )and a month(single select slicer) will be selected by default ex :2025 apr then I want to see Q1 and Q2 appearing in my chart with Q1 having only mar data and q2 having apr data
This measure is not working
- Kobes4 months ago
Advocate I
The '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?
- Harika054 months ago
Helper I
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
- Kobes4 months ago
Advocate I
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.