Average range by slicer

[lherbert501]

Hi there,

I currently have my dax code below that gives me the average over the previous 6 months which works correctly.

6MonthSalesAvg =

CALCULATE (

SUMX ( VALUES ( SalesOrders), [Total Sales] ),

DATESINPERIOD (RS_Dates[DateFull], MAX (RS_Dates[DateFull] ), -6, MONTH ), RS_Dates[IsWorkingDay]=true()

) /6

What I want to do is to give me the average of that month selected and the months previous.

E.g image 1 below would be 202201 - 202206 (6months)

Image 2 would be 202201 - 202205 with the 5 months and so on with less months. 

Is this possible? 

Thankyou

1)

2)

[v-jianboli-msft]

Hi @lherbert501 ,

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or if you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

Refer to:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-jianboli-msft · Accepted solution]

Hi @lherbert501 ,

Based on your description, I have created a simple sample:

Please try:

First create a table for slicer:

Then change the slicer to single select:

Then create a measure like this:

Average =

var _a = VALUE( RIGHT( SELECTEDVALUE('For Slicer'[Value]),2))

return SUMX(FILTER('SalesOrders',MONTH([Date])<=_a),[Total Sales])/_a

Final output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lherbert501]

Thankyou @v-jianboli-msft  🙂

[MahyarTF]

Hi,

In your dax function, check if the Month number is less than 6, then use StartOfYear('Date[Date]') in your date condition, otherwise use the current one(existing Dax that you wrote)

[lherbert501]

Hi @MahyarTF 

Thanks for the reply.

How would you check if its less than 6 and where would the start of the year be in the query?

I think this is the part im struggling with?

Thanks in advance

Liam

[MahyarTF]

If you have a Date dimension, you need to add the Number of months and then use it in your dax,

Otherwise, you could you the Right() function :

if (Right([Period], 2)>'06'), Date[Date] > Your Function && date[date] <= Max(Date[Date]),  date[date] >=  StartOfYear('Date[Date]') && date[date] <= Max(Date[Date])