Looking for Previous Period Comparision

[Rich_Wyeth]

Hi,

I am creating a report that needs to look at a date range selected and to look back a year at the same period.

I created some measures, example: 

M_TPRICE = SUM('Margin Reports'[T PRICE])

M_TPRICE_PreviousPeriod = CALCULATE([M_TPRICE],SAMEPERIODLASTYEAR('Margin Reports'[DATE]))

This works ok, until I want oto either add it to a table view, or filter down the results, say by a specific company.

Ideally I need to get this into a visual table so that I can show the difference between the current and previous period, then highlight if sales have dropped off.

What can I do to enable filtering in this scenario, as I don't think SAMEPERIODLASTYEAR is going to work with a filter.

Any help or suggestions would be gratefully received.

Kind Regards.

[Uzi2019]

Hi @Rich_Wyeth 

You can try with dateadd function .

 or check this video for better understanding

https://www.youtube.com/watch?v=e_pB4MzGO2I

https://www.youtube.com/watch?v=Wwo-tY0B9pY&t=428s

https://www.youtube.com/watch?v=fP8oxU2DE9w

These videos would help you to write comparison for dynamic period either day/ week/month/ year is selected in date range.

I hope I answered your question!

[bhanu_gautam]

@Rich_Wyeth , Use DATEADD function 

DAX
M_TPRICE = SUM('Margin Reports'[T PRICE])

Previous Period Measure:

DAX
M_TPRICE_PreviousPeriod = CALCULATE(
[M_TPRICE],
DATEADD('Margin Reports'[DATE], -1, YEAR)
)

Difference Measure:

M_TPRICE_Difference = [M_TPRICE] - [M_TPRICE_PreviousPeriod]

[Rich_Wyeth]

Hi,

One thing I do notice is that my date column, is just that, a date column. It is not a Calendar column, so doesn't split out into Day, Month, Year, Quarter, when I use it.

Could this be my problem, as dateadd doesn't work either.

How can I make the date column become a calendar, rather than just a date "Long Date".?

[bhanu_gautam · Accepted solution]

@Rich_Wyeth , Go to modelling create a new table using

DAX
Calendar =
ADDCOLUMNS (
CALENDAR (DATE(2020, 1, 1), DATE(2023, 12, 31)),
"Year", YEAR([Date]),
"Month", MONTH([Date]),
"Day", DAY([Date]),
"Quarter", QUARTER([Date]),
"MonthName", FORMAT([Date], "MMMM"),
"YearMonth", FORMAT([Date], "YYYY-MM")
)

Change date as per your dates and create a relationship of this with original date column