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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Turf03
Helper II
Helper II

Creating a measure to display dates of a specific year

Is there a way to create a meausure that only brings back the dates based on a specific year? 

 

Or is there a way to match dates from this year to last year based on day of the week? ie yesterday was Wednesday 9/2. Same day of week last year was 9/4

1 ACCEPTED SOLUTION

@Turf03 

Make sure you have a calendar table and it is linked to the sales table, also  a column added for the years
Create the following two measures for 2019 and 2020

 

2020  Sales =  Calculate ( Sum(Sales[Amount]), 'Calendar'[Year] = 2020 )

 

2019  Sales =  Calculate ( SUM(Sales[Amount]), Dateadd('Calendar'[Date],-364,Day),'Calendar'[Year] = 2019)

 
You can have ot in a single chart to compare next to each other by day. Check the tools tip I added the date as well

Last Year Date = Calculate ( Sum('Calendar'[Date]), Dateadd('Calendar'[Date],-364,Day))



Fowmy_0-1599229473262.png

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

9 REPLIES 9
Anonymous
Not applicable

Hi...I am really, really new to Power BI and DAX.  I'm building an HR Dashboard that has headcount, # of new hires, OT rate, and turnover rate.  On each dashboard, I have been asked to provide the data for 2020 and 2021 side by side.  Because they want to see them in that fashion, I have created measures that are specific to each year, plus that will allow me to have the information ready to go for the requests that I know will be coming even moving into 2022.  

That said, I cannot seem to filter the results so that the data is just for that year.  I have tried everything I can think of, but I just don't know enough.  For example, I have this measure created.

2020EECountEndofMonth =

VAR Max_Date = MAX('CalendarTable'[Date])

RETURN

CALCULATE(

COUNT('HR Data'[Payroll Name]),

FILTER(ALL('HR Data'),

'HR Data'[Hire/Rehire Date] <= Max_Date &&

('HR Data'[Termination Date] > Max_Date ||

'HR Data'[Termination Date] = BLANK())

))
It gives me the below results.  Jan - September are 2021 headcount numbers.  Then the data repeats for October through December.  bluestonegemini_0-1634581204279.png

Any help woudl be appreciated!

Thanks

Erin

 

 

amitchandak
Super User
Super User

@Turf03 last year the same weekday is 364 days behind. Use a date table to get this.

 

Year Week behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-364,Day))

 

Power BI — Day Intelligence
https://medium.com/@amitchandak.1978/power-bi-day-intelligence-questions-time-intelligence-5-5-5c324...

 

Does this return the value associated to the corresponding date or does it return just the date? I'm looking to return the actual date and not the value that occurred on that date

 

@amitchandak @Fowmy 

@Turf03 

Use can use this measure to get the datw:

Same day Last Year = CALCULATE( MAX('Calendar'[Date]), Dateadd('Calendar'[Date],-364,Day))

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Ok that seems to work but to expand the question even more...

 

Say I am trying to create a table with the Sales values of each day and the corresponding day of the previous year. 

 

How do I create the measures that show the dates of 2020, the corresponding day of 2019 and the a sales of each like the example below?

 

Furthermore, how would I create a line graph that shows the same data in a table plotting the sales data for each date and the dates of both years as the Axis?

 

2020 Date2020 Sales2019 Date2019 Sales
9/3/20202009/5/2019300

@Turf03 

Make sure you have a calendar table and it is linked to the sales table, also  a column added for the years
Create the following two measures for 2019 and 2020

 

2020  Sales =  Calculate ( Sum(Sales[Amount]), 'Calendar'[Year] = 2020 )

 

2019  Sales =  Calculate ( SUM(Sales[Amount]), Dateadd('Calendar'[Date],-364,Day),'Calendar'[Year] = 2019)

 
You can have ot in a single chart to compare next to each other by day. Check the tools tip I added the date as well

Last Year Date = Calculate ( Sum('Calendar'[Date]), Dateadd('Calendar'[Date],-364,Day))



Fowmy_0-1599229473262.png

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Edit.....nevermind I figured it out!!!! Thanks for the great advice!!!!

 

@Fowmy  excellent! 

 

Can you show me the line graph set up?

 

I can't get it to line up like yours, its shows as continuous.

 

Also how should the Calendar table be linked?

@Turf03 , date you can get like 

new column 

'Date'[Date] -364

 

new measure 

max('Date'[Date]) -364

Fowmy
Super User
Super User

@Turf03 

You can get same weekday last year Amount like

Measure = Calculate ( Sum(Table[Value]), Dateadd(Calender[Date],-364,Day))

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors