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

[Anonymous]

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:

[Greg_Deckler]

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

[Anonymous]

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

[Greg_Deckler]

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-TIT...

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

[Anonymous]

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

Test Report 

[Greg_Deckler]

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

[Anonymous]

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 

[Greg_Deckler]

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

[Anonymous]

Ah no dice, still blank 😞

But thank you!

[v-lid-msft · Accepted solution]

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,

[Anonymous]

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