Take Filter Selection and every number before it. Not a between filter though

[Anonymous]

So I have a date table with a non-typical fiscal calendar. This date table has groups of dates in each year called periods ranging from 1-13.

I'm trying to give the user the ability to choose to view a report period-to-date from a specific point in time.

So say period 1 has sales of $100 and period 2 has sales of $500. How can I let the user select period 2 and get a measure that results in $600?

I don't think I can use time intelligence because this a non-typical calendar. I think I'm trying to create a measure that first ignore's the user's period selection, but also only takes numbers less than the user's filter selection.

I don't know if I'm overthinking this.

[amitchandak]

@Anonymous , You can make Rank to work for you time intelligence create an ascending on your period based on period start rank 

////////Period 
Period Rank = RANKX(all('Date'),'Date'[Period Start date],,ASC,Dense)

This Period = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Period Rank]=max('Date'[Period Rank])))
Last Period = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Period Rank]=max('Date'[Period Rank])-1))
Last 12 Period Sales = CALCULATE(SUM(Sales[Net Sales]),FILTER(all('Date'),'Date'[Period Rank]>=min('Date'[Period Rank])-12 
				&& 'Date'[Period Rank]<=max('Date'[Period Rank])))

Last 8 period Sales =
Var _min = maxx(allselected('Date','Date'[Period Rank]) -8
Var _max = maxx(allselected('Date','Date'[Period Rank]) 
CALCULATE(SUM(Sales[Net Sales]),FILTER(all('Date'),'Date'[Period Rank]>=_min && 'Date'[Period Rank]<=_max))

Refer my blog on non-standard

https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p/881739

Very similar to week

https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123

Please Watch/Like/Share My webinar on Time Intelligence: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184
My Youtube Tips at: https://www.youtube.com/playlist?list=PLPaNVDMhUXGYrm5rm6ME6rjzKGSvT9Jmy

Appreciate your Kudos.

[Anonymous]

@mahoneypat That works perfectly thank you!

I have one more related question as I also want to do period over period calculations. I know how to just get the previous period from the one selected by adjusting your formula above. That's easy. But I don't want to do that because if period 1 is selected it will try to look at period 0 which doesn't exist.

So I created a ranking column in my Dates table for all the periods spanning over the years. I tried adjusting your formula above to return my measure for previous period of the one selected but based on my ranking value. So I added a lookupvalue to the variable portion to return the period rank value. I know that part works since I tested it in another measure. I get blanks when trying to reutrn a value in the total formula though. I think it's due to the ALL portion that might have to factor in the ranking column?

[mahoneypat]

For your period over period calculation, you will have the two variables - currentperiod and prevperiod (or whatever you've called them).  You have at least two options to avoid an error when there is no previous period.

1.  Use DIVIDE(currentperiod-prevperiod, prevperiod) -- this will protect the divide by 0 (blank) and return blank

2.  Or you can use an IF() in your return -- IF(isblank(prevperiod), blank(), (currentperiod - prevperiod)/prevperiod

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[mahoneypat · Accepted solution]

This is a common pattern and can be done with an approach like this (assuming you have a visual that has the Period column in it)

PeriodToDate = var currentperiod = selectedvalue('Date'[Period])

return calculate([your measure], Filter(All('Date'[Period]), 'Date'[Period]<=currentperiod)

Depending on what other columns you have in your visual, you may need to change the ALL() part and maybe add other filter(s) to your calculate, but this should work in a simple table with Period and your measure.

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat