cancel
Showing results for
Did you mean:
MTam Frequent Visitor

Cutting a selected period from a timeline

Dear All,

I would have a question how can I select in DAX an exact period, if there are data before and after that. But I attach a picture, and some measure to better understand this issue: Also here I would like to show only the months, where I have values for both calculations. (from 01/2018 to 06/2018). It is important, that this solution would be dynamic, also in next year starts from 01/2019...

For this I used the following calculations:

Actual = DIVIDE([SUM_NNS],[SUM_NNS_SPLY])-1

Same period last year = CALCULATE(DIVIDE([SUM_NNS],[SUM_NNS_SPLY])-1,SAMEPERIODLASTYEAR('Calendar'[Dates]))

SUM_NNS = SUM(Finance[NNS])

SUM_NNS_SPLY = CALCULATE([SUM_NNS],SAMEPERIODLASTYEAR('Calendar'[Dates]))

I was thinking on many possible solution, but I didn't find any solution.

Thanks for the help!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Sean Super Contributor

Re: SameRe: Cutting a selected period from a timeline

I'm not sure I understand exactly what you mean.

Do you want the chart to show both years only up to the current month?

Actual =
CALCULATE (
DIVIDE ( [SUM_NNS] - [SUM_NNS_SPLY], [SUM_NNS_SPLY], 0 ),
FILTER ( 'Calendar', 'Calendar'[Month Number] <= MONTH ( TODAY () ) )
)

You can replace TODAY ( ) in the above to get a value from your data set. 4 REPLIES 4
Sean Super Contributor

SameRe: Cutting a selected period from a timeline

Change your Measure to this

Actual = DIVIDE ( [SUM_NNS] - [SUM_NNS_SPLY], [SUM_NNS_SPLY], 0 )

And change this too...

Same period last year =
CALCULATE ( [Actual], SAMEPERIODLASTYEAR ( 'Calendar'[Dates] ) )

That should do it! MTam Frequent Visitor

Re: SameRe: Cutting a selected period from a timeline

Dear Sean,

Thanks for the quick answer! But I would to show year by year only the actual year related data, like this: Ok, it's a cheating because I startes the Y axis from 0, and so the negative values aren't displayed. But the point is, that the same period last year (2017) is displayed for every month in actual year, but the actual (2018) only for the past months where were values. And it shows everytime the actual year (one year)

I can reach it with filtering date in filters pane, but I should solve it with a DAX calculation.

Thanks again.

Highlighted
Sean Super Contributor

Re: SameRe: Cutting a selected period from a timeline

I'm not sure I understand exactly what you mean.

Do you want the chart to show both years only up to the current month?

Actual =
CALCULATE (
DIVIDE ( [SUM_NNS] - [SUM_NNS_SPLY], [SUM_NNS_SPLY], 0 ),
FILTER ( 'Calendar', 'Calendar'[Month Number] <= MONTH ( TODAY () ) )
)

You can replace TODAY ( ) in the above to get a value from your data set. MTam Frequent Visitor

Re: SameRe: Cutting a selected period from a timeline

I modified this a bit:

Actual =
CALCULATE (
DIVIDE ( [SUM_NNS] - [SUM_NNS_SPLY], [SUM_NNS_SPLY], 0 ),
FILTER ( 'Calendar', 'Calendar'[Year] = YEAR ( TODAY () ) )
)

Now only shows data from begining of the actual year, but we need to take an attention on "same period last year" calculation, and we should calculate with your version of actual calculaiton! Thank you so much for your help!)

Announcements Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge. Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform. Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag. Top Kudoed Authors
Users Online
Currently online: 194 members 2,017 guests
Recent signins:
• Kalon • NoahLull • bwhitlock • thisisausername • tkashanatti • emudria • Frankie365 • cald • leilei787 • simongreen • pyhchan • peterkim • poconnor • branden_dahlem 