Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Rich_Wyeth
Frequent Visitor

Looking for Previous Period Comparision

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




Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!




LinkedIn






View solution in original post

4 REPLIES 4
Uzi2019
Super User
Super User

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!

 

 

Don't forget to give thumbs up and accept this as a solution if it helped you!!!
bhanu_gautam
Super User
Super User

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




Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!




LinkedIn






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".?

@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




Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!




LinkedIn






Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.