Creating a measure to display dates of a specific year

[Turf03]

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

[Anonymous]

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.  

Any help woudl be appreciated!

Thanks

Erin

[amitchandak]

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

[Turf03]

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 

[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 

[Turf03]

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 Date	2020 Sales	2019 Date	2019 Sales
9/3/2020	200	9/5/2019	300

[Fowmy · 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))

________________________

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

[Turf03]

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?

[amitchandak]

@Turf03 , date you can get like 

new column 

'Date'[Date] -364

new measure 

max('Date'[Date]) -364

[Fowmy]

@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