Forum Discussion

Lucian's avatar
Lucian
Icon for Responsive Resident rankResponsive Resident
6 years ago
Solved

Return an interval starting from single selection

Hello,

It might sound like a stupid question but in this very moment I cannot "see" how could I solve it nor find an "enlighting" article, so I have to ask you. 

 

I need to calculate a measure some periods back in time starting from a selected date and display columns with "Current Period" then go back some months.

 

Balance by Period =
CALCULATE (
    [Balance],
    ( DATEADD ( Dates[Date], MIN ( 'Periods'[MonthsBack] ), MONTH ) )
)

 
For this to work I have created a "Period" table
 
All is calculating perfectly and I could see all the periods as columns in my matrix until the user want to select just a specific number of months back.
So, putting a slicer (slider with "less than or equal to") for the Order column of my Period table was again working like a charm. But now comes the "blind" part for me - The slicer must be a "list with single select".
 
So the question is how to achieve the same starting from a single selected value?
 
 
And here is a test file if needed: AchieveBetweenFromSingleSelect.pbix 
 
Kind Regards,
Lucian
 
Need Help
  • v-easonf-msft's avatar
    v-easonf-msft
    6 years ago

    Hi , Lucian 

    Create a seperated table to replace the field "Order" in the slicer:

     

    slicer_table = DISTINCT(Periods[Order])

     

    Then apply the following measure to filter pane.

     

    Slicer  control = 
var order1=SELECTEDVALUE(Periods[Order])
var order2=SELECTEDVALUE(slicer_table[Order])
return IF(order1<=order2,1,0)

     

     

    pbix attached

     

    Best Regards,
    Community Support Team _ Eason
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

5 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    Lucian , Not sure the objective of this , you try with date calendar

     

    Month behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Month))

    2 Months behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-2,Month))

    3 Months behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-3,Month))

     

    MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
    last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
    last MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
    last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))
    last year MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))

     

     

    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/

    See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


    Appreciate your Kudos.

    • Lucian's avatar
      Lucian
      Icon for Responsive Resident rankResponsive Resident
      6 years ago

      Hello amitchandak 

       

      And thank you for your quick answer. The problem presented in my initial message is a part of a "bigger picture". That's why I have tried to reproduce a smaller part that involving filtering the "Periods" table.

       

      What I need is a "variable months back". I have already achieved it using the Periods table but showing the values as a matrix with  "variable number of columns" using a slicer with slider.

       

       

      What I want to achieve is a "variable number of columns" based on the rows of the Periods table but not using the slider but a single selected value (in the same range 1-13).

       

      With other words, after the user select a Year-Month I have to go back as many months user wants and I have it one way but I need another kind of selection.

       

      Or if rephraze the question from my first message: How could I obtain the rows from the Periods table from 1 to 3 or from 1 to 10 or from 1 to whatever maximum value the user will select from a dropdown list?

       

      I hope I get clearer now. 🙂

       

      Kind Regards,

      Lucian