cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MTam Frequent Visitor
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:

 

Period.png

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
Super User
Super User

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. Smiley Happy

4 REPLIES 4
Highlighted
Super User
Super User

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! Smiley Happy

MTam Frequent Visitor
Frequent Visitor

Re: SameRe: Cutting a selected period from a timeline

Dear Sean,

 

Thanks for the quick answer!Smiley Happy But I would to show year by year only the actual year related data, like this:

 

Period.png

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.

 

Super User
Super User

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. Smiley Happy

MTam Frequent Visitor
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!Smiley Happy

 

Thank you so much for your help!)