Calculate Set of Data for Last 3 Months for an existing measure

[PeteD1302]

I have viewed numerous posts related to this topic and unable to resolve this DAX query; the query returns no data.

Last 3 Months = CALCULATE(SUMX(Table1, Table1[SLA]), DATEADD(Table1[YYYY-MM],-3,MONTH))

I have a data table, as below and want to sum the data [for SLA] for the last 3 months (I have already created a separate DATE table and linked it to YYYY-MM in table1).

I have a Measure that does not appear in the table named SLA, reported as a percentage, based on the count of projects = Yes for SLA_Ok.

Table1

Project	Type	YYYY-MM	YYYY_Qrt	SLA_Ok
120467	Type4	Oct-19	2019_Q4	Yes
118040	Type4	Oct-19	2019_Q4	No
120678	Other	Sep-19	2019_Q3	Yes
120077	Type2	Sep-19	2019_Q3	Yes
119239	Type3	Aug-19	2019_Q3	Yes
118718	Other	Aug-19	2019_Q3	No

[Ashish_Mathur]

Hi,

This would be my approach

1. Ensure that the entries in YYYY-MM column are actual date entries
2. Create a Calendar Table with the following formula: Calendar = CALENDAR(MIN(Table1[Date]),MAX(Table1[Date]))
3. In the Calendar Table extract Year and Month with the following calculated column formulas: Year = YEAR(Calendar[Date]) and FORMAT(Calendar[Date],"mmmm")
4. Create a relationship from the Date column of Table1 to the Date column of the Calendar Table
5. Build 2 slicer from the Calendar Table, one each for Year and Month and select October and 2019
6. Write this measure: Measure = CALCULATE([SLA],DATESBETWEEN(Calendar[Date],EDATE(MIN(CALENDAR[Date]),-2),MAX(Calendar[Date])))

Hope this helps.

[PeteD1302]

Thanks Ashish, I have followed your advice.

I am getting a DAX syntax error as shown below

Date Table                                                  Table1

New Column DAX

[Ashish_Mathur]

Hi,

You are using ]].  Use only ].

[amitchandak]

in case you want to use max, you need to use the filter clause. Refer example.

Sale_till_tody =CALCULATE(sum(Sales[Sales Amount]),filter(sales,Sales[Sales Date]<=maxx(Sales,Sales[Sales Date].[Date])))

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[PeteD1302]

Thanks Amitchandak, I did have a look at your example DAX.

The complication I have is that the column of interest, SLA expressed as a %, is not in the table, as it is a calculated measure, so I need to use sumx instead of sum.

I tried these 2 variants; the first one showed the row count, the second one had the same value for all the display table entries.

Last 3 Months SLA =
VAR _Cuur_start = Min('Last 3 Months Date'[Last 3 Months])
VAR _Curr_END = Max('Last 3 Months Date'[Last 3 Months])
return
calculate(SUMX(table1, table1[SLA])))

Last 3 Months SLA2 =
CALCULATE(sumx(table1, table1[SLA]),filter(table1,table1[SLA]<=MAXX(table1,table1[YYYY-MM])))

[PeteD1302 · Accepted solution]

From the help I received and a bit more searching I have been able to solve this as below;

Converting calculations to measures.
Last 3 Month Count =
CALCULATE([Count],DATESBETWEEN(table1[YYYY MM],EDATE(MIN(table1[YYYY MM]),-2),MAX(table1[YYYY MM])))

Count = COUNTROWS('table1')

Last 3 Months SLA =
CALCULATE ([SLA],DATESBETWEEN('table1'[YYYY MM], EDATE (MIN('table1'[YYYY MM]),-2), MAX('table1'[YYYY MM])))

YYYY MM in date format; e.g. Tuesday, 1 October 2019
Creating a Date table for YYYY MM (with Month and Year columns)