- Forums
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Integrations with Files and Services
- Mobile Apps
- Developer
- DAX Commands and Tips
- Let's Talk Data
- Custom Visuals Development Discussion
- Community Support
- Community Information
- Community Accounts & Registration
- Using the Community
- Community Feedback
- Training and Consulting
- Dashboard in a Day
- EdX Specific Training Discussion Forum

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Re: SameRe: Cutting a selected period from a timel...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

MTam

Frequent Visitor

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-09-2018
10:08 AM

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!

Solved! Go to Solution.

1 ACCEPTED SOLUTION

Accepted Solutions

Sean

Super User

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-09-2018
11:46 AM

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 User

SameRe: Cutting a selected period from a timeline

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-09-2018
10:30 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-09-2018
11:07 AM

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

Super User

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-09-2018
11:46 AM

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.

Highlighted
##

MTam

Frequent Visitor

Re: SameRe: Cutting a selected period from a timeline

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-09-2018
09:55 PM

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