Return an interval starting from single selection

[Lucian]

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

[amitchandak · Accepted solution]

@Lucian , see if this can help

https://www.youtube.com/watch?v=duMSovyosXE

[amitchandak]

@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-Y...


Appreciate your Kudos.

[Lucian]

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

[v-easonf-msft · Accepted solution]

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.

[Lucian]

Hi @v-easonf-msft 

Indeed your answer did the trick, and also the topic recommended by @amitchandak was inspiring, because both of you reminded me (and I quote from the Inception movie):

"a truth that she once knew...but chose to forget"

In my case the "disconnected table" were the words "I choose to forget"... 😁

My mistake was using the Order column from the "Periods" table for the slicer.

So thank you both for reminding me. 🙂

[amitchandak · Accepted solution]

@Lucian , see if this can help

https://www.youtube.com/watch?v=duMSovyosXE