Forum Discussion
Resolver IThis year vs Last year
How can I show a set of values (based on user selected date range) on one line, and a 2nd line which shows the data for the same time (months) but of the previous year to the user selected date range please?
Here is what I have tried:
I'm having some troubles trying to do a YOY line chart that shows months on the bottom, and the values/count of current events vs the previous year.
I have pulled in a dataset that is similar to this
ID | DateOfEvent |
1 | 01-01-2019 |
2 | 02-02-2019 |
3 | 03-03-2019 |
4 | 01-01-2018 |
5 | 02-02-2018 |
6 | 03-03-2018 |
... | ... ... |
I then created a measure
Events - number of = COUNT('EventsTable'[id])
Followed by a Previous Year measure.
Previous Year - # of events = CALCULATE('EventsTable'[Events - number of], SAMEPERIODLASTYEAR(EventsTable[DateOfEvent]))It seemed I needed "Events - Number of" in order to create "Previous Year - # of events" (I couldn't select a column, but I could select a measure?)
I also created a date table with CALENDARAUTO() and linked it to the DateOfEvent column, but I keep getting the same contigious values warning message. If I remove the "Previous Year - # of events" measure the problem goes away but then I don't have the data I want to show?
Hope I've provided enough detail/explanation.
thanks
Solved my own problem.
In case any other new to PowerBI/Dax comes this way. I had to create a new table and used CALENDARAUTO() to generate that.
I then linked it to the date of events in my dataset.
I hadn't set the measure below to take from the new date table that I created
Previous Year - # of events = CALCULATE('EventsTable'[Events - number of], SAMEPERIODLASTYEAR(EventsTable[DateOfEvent]))When corrected to
Previous Year - # of events = CALCULATE('EventsTable'[Events - number of], SAMEPERIODLASTYEAR(DateTable[Dates]))and I used that date table in the visual, things worked ! :)
1 Reply
- RobThrive
Solved my own problem.
In case any other new to PowerBI/Dax comes this way. I had to create a new table and used CALENDARAUTO() to generate that.
I then linked it to the date of events in my dataset.
I hadn't set the measure below to take from the new date table that I created
Previous Year - # of events = CALCULATE('EventsTable'[Events - number of], SAMEPERIODLASTYEAR(EventsTable[DateOfEvent]))When corrected to
Previous Year - # of events = CALCULATE('EventsTable'[Events - number of], SAMEPERIODLASTYEAR(DateTable[Dates]))and I used that date table in the visual, things worked ! :)