Cutting a selected period from a timeline

[MTam]

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!

[Sean]

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]

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.

[Sean · Accepted solution]

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]

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