Comparing daily counts year over year

[mikegee78]

Hello-

I am new to Power BI and I have been tasked to create a dashboard where I am comparing traffic counts by day between last year and this year to show the impact of COVID-19.  I would like to do this in a stacked bar chart, but I cannot get it to work.  The data is an excel dump from an SQL database (I'm working remotely so I can't access the database itself) in the form of monthly files.  I've appended these into both yearly queries and a single query with all the data from both years.  What is the best way to approach this?  Thanks!

Mike

[v-xicai · Accepted solution]

Hi @mikegee78 ,

You may create a calendar table first, then create relationship with your fact data table on date field.

Calendar=CALENDARAUTO

Then create measure like DAX below.

Current year count=SUM(Table[Traffic count])

Last year count= CALCULATE(SUM(Table[Traffic count]),DATEADD(Calendar[Date],-1,Year))

Best Regards,

Amy 

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DataZoe]

You could create a measure for

[Traffic] = sum(Traffic[Traffic Counts])

and

[Traffic Last Year] = calculate([Traffic],dateadd(Traffic[Date],-1,year))

Make sure that [Date] is a date or date/time column (or from a calendar table that's marked as a date table).

[Mariusz]

Hi @mikegee78 

You can use SAMEPERIODLASTYEAR DAX function please see the link below.
https://docs.microsoft.com/en-us/dax/sameperiodlastyear-function-dax

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[amitchandak]

@mikegee78 , You can create a year behind the measure

example

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

Or Same weekday last year

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

and can compare with your date