topic Re: Create Measure that shows Previous Period of period selected in Date Slicer in Desktop

Create Measure that shows Previous Period of period selected in Date Slicer

Anonymous — Thu, 09 Apr 2020 17:14:24 GMT

Good Day and thanks in advance for any help.

I am attempting to create a measure that finds Sales Revenue for the parallel period (for the previous year) selected in my Time Period selector.

To explain: I have a table that shows Market Classification, Revenue, Total Costs, Gross Profit and Gross Profit Rate. I have also a Time Period Slicer that changes all of the above values to Current Month to Date, Current Year to Date, MTD Last Year, YTD Last Year, etc. I am attempting to add another column that will compare the revenue in the selected time period with the revenue of the parallel period the prior year.

So if I select Current Quarter to Date from the selector, then I would like this new column to show QTD Revenue for 2019 . I cannot figure out what measure to use to do this.

I am sorry I cannot share data for this, there is too much proprietary info to strip.

Thank you!

Here is what I am trying to do:

Re: Create Measure that shows Previous Period of period selected in Date Slicer

Greg_Deckler — Thu, 09 Apr 2020 18:44:16 GMT

Generally you wrap your measure in a CALCULATE and use SAMEPERIODLASTYEAR or PARRELPERIOD

Re: Create Measure that shows Previous Period of period selected in Date Slicer

Anonymous — Thu, 09 Apr 2020 18:55:22 GMT

Hi Greg_Deckler Thanks! I have tried both and they are coming up blank. I have used:

= CALCULATE([REVENUE],PARALLELPERIOD('Calendar'{[Date],-1,YEAR)) and

= CALCULATE([REVENUE],SAMEPERIODLASTYEAR('Calendar'{Date])

Likely has to do with my Time Period table used for the slicer? I will try to get some data together quickly to show

Re: Create Measure that shows Previous Period of period selected in Date Slicer

Greg_Deckler — Thu, 09 Apr 2020 19:07:32 GMT

Ah, yes, probably. You are probably not using a separate date table marked as a date table. That's when you can use time intelligence functions.

See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TITHW/m-p/434008

I would need to understand your data better to figure out how to help specifically. Let me reread the post.

Re: Create Measure that shows Previous Period of period selected in Date Slicer

Anonymous — Thu, 09 Apr 2020 19:36:04 GMT

Greg_Deckler Here is a sample report, hopefully it helps/works!

Test Report

Re: Create Measure that shows Previous Period of period selected in Date Slicer

Greg_Deckler — Thu, 09 Apr 2020 19:39:28 GMT

It's a PBIT file so I it doesn't load any data other than calendar

Re: Create Measure that shows Previous Period of period selected in Date Slicer

Anonymous — Thu, 09 Apr 2020 19:44:27 GMT

Yeahhhh I havent shared anything on here before so I figured I would do it wrong. Trying again! (thanks for being patient!)

Report Take 2

Re: Create Measure that shows Previous Period of period selected in Date Slicer

Greg_Deckler — Thu, 09 Apr 2020 20:04:54 GMT

OK, looking at this, perhaps you could do something like this:

Previous Year Revenue = VAR __Min = MIN('Period'[Date]) VAR __Max = MAX('Period'[Date]) VAR __LastYearMin = DATE(YEAR(__Min)-1,MONTH(__Min),DAY(__Min)) VAR __LastYearMax = DATE(YEAR(__Max)-1,MONTH(__Max),DAY(__Max)) RETURN SUMX(FILTER('Sales','Sales'[Time Entered] >= __LastYearMin && 'Sales'[Time Entered] <= __LastYearMax),'Sales'[Revenue])

Re: Create Measure that shows Previous Period of period selected in Date Slicer

Anonymous — Thu, 09 Apr 2020 20:15:23 GMT

Ah no dice, still blank 😞

But thank you!

Re: Create Measure that shows Previous Period of period selected in Date Slicer

v-lid-msft — Fri, 10 Apr 2020 03:12:48 GMT

Hi Anonymous ,

We can try to use a all function in a measure to get correct result (by removing the period filter when calculate the last year):

Measure = CALCULATE('Sales'[Profit],ALL(Period[Period]),SAMEPERIODLASTYEAR('Calendar'[Date]))

If it doesn't meet your requirement, Could you please show the exact expected result based on the Tables that you have shared？

Best regards,

Re: Create Measure that shows Previous Period of period selected in Date Slicer

Anonymous — Fri, 10 Apr 2020 21:14:41 GMT

v-lid-msft YES!! One change: the measure is for Revenue and not profit, but you got it. I tried something similar as well previously but had the syntax wrong and with all my other failed attempts had lost all hope. Thank you Thank you!