Rolling 12 month min / max

[MungBurger]

I have this DAX that works in DAX Studio and returns the expected value.

DEFINE 
VAR DateFilter =   FILTER(
    KEEPFILTERS(VALUES('DIM Date'[Full Date])),
    AND(   'DIM Date'[Full Date] >= DATE(2019, 1, 1),
      'DIM Date'[Full Date] < DATE(2020, 1, 1)    )  )

var tbl =   TOPN(    100,
    SUMMARIZECOLUMNS(
      'DIM Date'[Month Year Short Description],     
      DateFilter
      ,"A1", [Availability]    )  )

VAR R = GROUPBY ( tbl,     
 "A2", MAXX( CURRENTGROUP(), [A1])  )

EVALUATE
R

I'm wanting the measure to look back 12 months and return the maximum monthly value in that period.

When the expression is created in SSAS Tabular Cube or Power BI (after replacing Evaluate with return)  I get this error message.

SummarizeColumns() and AddMissingItems() may not be used in this context.

Any help on how to rewrite this, as a single measure that will work, would be greatly appreciated, thanks.

[MungBurger]

@Ashish_Mathur @v-kelly-msft @amitchandak 

The solution is proving to be more complex than I'd expected

I've provided a PBIX file with sample data and also a hardcoded calculation that returns the expected value.

The expected value is correct for the date level of the year, month and day as in the visualization.

There will be other dimensions - ie org chart, but I've not included that for this example.

Thanks in advance for your assistance.

[Ashish_Mathur]

Hi,

I do not agree with the figures that you are showing in the ExpectedValue column.  I think the correct results are what you see in the Measure column.  You may download my PBI file from here.

Hope this helps.

[amitchandak]

@MungBurger, I agree with @Ashish_Mathur , I am also getting similar values.

https://www.dropbox.com/s/lu11wi9yjl58aqu/Rolling%2012%20month%20min%20or%20max.pbix?dl=0

[MungBurger]

@Ashish_Mathur @amitchandak 

My Apologies, there was a typo in the expected value measure. @Ashish_Mathur your solution calculates the correct monthly values but the year and Total values are out. For the 2019 Year level and the Total, I'd expect to see 0.6. @amitchandak your monthly values are not correct but the Year and Total Levels are showing the correct wrong value. ie Dec 2019 is .11 and so is 2019 year and Total. So if the Month calc was corrected, the year and total should follow. I'll have a look at combining the 2 calculations tomorrow. Again thanks for persistent assistance.

Months OK, Year and Total wrongMonth Wrong but the Year and Total show the correct wrong value.

[Ashish_Mathur · Accepted solution]

Hi,

You may download my revised PBI file from here.

Hope this helps.

[MungBurger]

@Ashish_Mathur Excellent, thanks for persisting.
Now i'm off to understand the expression syntax.

[Ashish_Mathur]

You are welcome.

[amitchandak]

Updated the file again. check now

[amitchandak]

In case you have date dimension. try like

measure 
var _last12 =Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-12,MONTH))  
return
maxx(summarize(date,date[Month-year], "_sum",CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-12,MONTH))) ,[_sum])

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s.

Refer
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi

https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

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 -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[MungBurger]

I'm a little confused now.

The measure was created as follows

VAR E = calculate(LASTDATE ( MetricAgg[FactDate] )) 
VAR S = DATEADD(LASTDATE ( MetricAgg[FactDate] ),-1,YEAR)  
VAR R = MINX ( SUMMARIZE ( 'MetricAgg', 'DIM Date'[Month Year Short Description], "MonthValue", 
CALCULATE ([Availability] ,DATESINPERIOD (MetricAgg[FactDate], LASTDATE ( MetricAgg[FactDate] ),-1,YEAR) ,
MetricAgg[MetricID] = 7 ) ) , [MonthValue]) 
RETURN R 

When I execute in SSMS the dates (created as seperate measures to see the values) behave as expected and give the correct range.

However I'd expect the min value in the previous 12 months to be returned.

In this case .843 was the Feb value and for the remainder of the year was the lowest value. For Feb to Dec I'd expect to see .843

 

[Ashish_Mathur]

Hi,

Try this measure

=MINX(DATESBETWEEN(Calendar[Date],EDATE(MIN(Calendar[Date]),-11),MAX(Calendar[Date])),SUM('Fact'[Availability]))

Hope this helps.

[v-kelly-msft]

Hi @MungBurger ，

First create a calculated column to get the year:

Column = YEAR('Table'[Month and year ])

Then create a calculated column to get the value you need:

Column 2 = CALCULATE(MIN('Table'[Availability]),ALLEXCEPT('Table','Table'[Column]))

And you will see:

For the related .pbix file,pls click here.

Best Regards,
Kelly

[MungBurger]

@v-kelly-msft Thanks, close but not quite.

Needs to be a measure not a column as there are more dimensions involved.

The period is the prior 12 months - not the calendar year. Mar 18 min would apply from March 18 to Feb 2019

This calc returns the same value as the availability measure each month. It does not look back over the past 12 months.

CALCULATE(MINX('Fact','Fact'[Availability]),ALLEXCEPT('Fact','Fact'[YYYYMM]),DATESBETWEEN('Fact'[dt],DATEADD(LAStDATE('Fact'[Dt]),-1,YEAR),LASTDATE('Fact'[Dt])))