Forum Discussion
Chart with constant limits
I have a table of data for which I want to create an upper limit based on the last 6 calendar month of data per category. See example:
| Issued Date | Type | Count |
| 2/5/2022 | New | 234 |
| 4/2/2022 | Rework | 45 |
| 11/9/2022 | New | 44 |
| 1/5/2023 | Returned | 7 |
| 2/9/2023 | Completed | 500 |
I want to show the mean for ever six month per category. For example: the code would check the data for type (New), but only for dates of (July 2022 - Dec 2022). I tried a measure and it gave the right value when tested in a label, but once I add it to a chart it changes every month.
6M_H1_2023_New Mean =
VAR StartDate = DATE(2022, 7, 1)
VAR EndDate = DATE(2022, 12, 30)
RETURN
DIVIDE(
CALCULATE(
COUNTROWS('Records Sheet'),
'Records Sheet'[Type] = "New",
'Records Sheet'[Issued Date] >= StartDate,
'Records Sheet'[Issued Date] <= EndDate
),
6,
0
)
Also, what is the best way to have avoid creating a measure for every category every 6 month for limits?
11 Replies
As a best practice, add a date dimension in your model and use it for time intelligence calculations. Once the date dimension is added, mark it as a date table on table tools. Check the related videos on my YT channel
Add Date Dimension
Importance of Date Dimension
Mark date dimension as a date table - why and how?
Time Intelligence Playlistand now you can easily add a measure like this:
Last 6 Months = CALCULATE ( COUNTROWS ( Table ), DATESINPERIOD ( 'Calendar'[Date], MAX( 'Calender'[Date] ), -6, MONTH ) )and wherever you are visualization date data, use a column from the calendar dimension.
I read about the date table, and that is my plan if the direct measure doesn't work. My issue is that it's not based on the last 6 month, the limits are either Jan - Jun or July - Dec. I don't understand why my measure is changing with the daily data, even though I specified the start and end in the formula.
I am also testing a calender table with a start and end month for each day, where I can add a column for mean and use the formula to look at the date and use the start and end dates to make a fixed 6 month fixed lines. I was hoping the measure idea works as it's faster.
I will take a look at the videos to see if I can apply the same to my scenario.
I need to have a static limit for the data that is based on the last 6 calendar months (if month is Jan -June, the limit is July-Dec (previous year), and if month is Jule-Dec, the limit is (Jan - June (same year)). I get the mean, then standard deviation, and then I can establish an upper action limit (action limit = x3 Standard deviation). Hence, the limit needs to be a straight line that changes every 6 month.
Beho so the last 6 months are based on today's date, then it becomes super easy:
Measure = VAR __Today = TODAY () VAR __MaxDate = IF ( MONTH ( __Today ) <= 6, DATE ( YEAR ( __Today ) - 1, 12, 31 ), DATE ( YEAR ( __Today ), 6, 30 ) ) VAR __MinDate = EOMONTH ( __MaxDate, -6 ) + 1 RETURN CALCULATE ( [Your Measure], DATESBETWEEN ( 'Calendar'[Date], __MinDate, __MaxDate ) )I like the idea of calculating the dates instead of having them as part of the equation. Other than this part, the formula is the same as the one I have in the original post. When I tried that it worked in a label, but if I add it to a chart, it is not static, it has a different value every day still somehow.
Hi Parry2k,
I tried adding the DAX as a measure and it wouldn't allow me to select the date field, so I added a calculated column:
6MM_Execution Erron Mean = VAR EndDate = IF ( MONTH ( 'Records Sheet'[Issued Date] ) <= 6, DATE ( YEAR ( 'Records Sheet'[Issued Date] ) - 1, 12, 31 ), DATE ( YEAR ( 'Records Sheet'[Issued Date] ), 6, 30 ) ) VAR StartDate = EOMONTH ( EndDate, -6 ) + 1 RETURN DIVIDE( CALCULATE( COUNTROWS('Records Sheet'), 'Records Sheet'[Type] = "Execution Error", 'Records Sheet'[Issued Date] >= StartDate, 'Records Sheet'[Issued Date] <= EndDate ), 6, 0 )And when I add this column in my chart, it doesn't add anything.
Is that the right way to approach it? if I want to do it as a measure, is there a way to workaround not being able to select it.
I still need some help with this one. To summarize the scenario I have. I have daily data with some categories, that I am trying to use to create monthly sum, and then a 6 month rolling average based on the monthly sum (based on 6 month of monthly data with 6-month gap). I would create separate measures for the different categories and get the monthly mean accordingly. I have tried several methods and DAX and they either return the daily average or just a blank value.
