Forum Discussion
Previous Year Measure completely wrong
Figured it out!
Took me a while to download the 800MB model over my phone :o
The correct DAX formula should be
Booked Sales YAGO = CALCULATE(SUM(vw_fact_booking[net_total_amt]),SAMEPERIODLASTYEAR(dates[date]))
The PREVIOUSYEAR calc was taking the whole previous year into account even if you were only selecting a month from the current year. Useful for somethings, but not for you.
Hi Anonymous
Just wondering if you have gaps in dates in your data.
I strongly recommend you use the pattern of adding a date/calender table to your model. A quickfire way to do this is using the following DAX calculate table statement.
dates = CALENDARAUTO()
When you have a date table, create a relationship between the [date] column and your vw_fact_booking[booking_date] column.
Change your measure to use the date column in the Date table and use the column from the date table in your slicer.
Does that make sense?
Create a relationship
HI Phil_Seamark, what if we have multiple dates that we want to analyse ?
I.E. we have booking_date and arrival_date and there is a need to look at both in same reports. However, I noticed that PowerBI only allows one date relationship at a time?
I tried your suggestion anyway, and it's still broke.
Booked Sales YAGO = CALCULATE(SUM(vw_fact_booking[net_total_amt]),PREVIOUSYEAR(dates[Date]))
Hi Anonymous
You can have multiple relationships between your sales and date table. Only one can be active. Active just means default, in that you can write minimal DAX code that assumes the rules of the active relationship. You can still analyise using the other dates, only you need to write your calculations to specify which of the relationships you'd like you use.
Hi Phil_Seamark - please see my edit to last post