Forum Discussion

lherbert501's avatar
lherbert501
Post Prodigy
4 years ago
Solved

Average range by slicer

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)

 

 

 

 
Need Help
Tips and Tricks
  • v-jianboli-msft's avatar
    v-jianboli-msft
    4 years ago

    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.

6 Replies

  • MahyarTF's avatar
    MahyarTF
    Memorable Member
    4 years ago

    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's avatar
      lherbert501
      Post Prodigy
      4 years ago

      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's avatar
        MahyarTF
        Memorable Member
        4 years ago

        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])

  • v-jianboli-msft's avatar
    v-jianboli-msft
    Community Support
    4 years ago

    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.

  • v-jianboli-msft's avatar
    v-jianboli-msft
    Community Support
    4 years ago

    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.