Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I am trying to know the variation of the number of reservations between 2 dates for each agency. Reservation table contains reservation number and date and agency table is ralated to this table throuh agency id.
I can get the variation result of the total amount of reservations but when I add the agency value to the report to know the variation for each agency I get the DATEADD contiguous error.
In this forum I could read as a solved problem using sumx or if(hasonevalue... so I used in my formula, the error disapears but the results I get are not correct when using Sumx and also doesn't give a total result but only individual results when I tried with" if(hasonevalue..."
The original formulas I used are:
Dates = calendar(min(RESERVATION[day]);max(RESERVATION[day]))
Num of dif reserv = distinctcount (RESERVATIONS[Reservation ID]
Num of dif reserv same day LY= calculate([Num of dif reserv];DATEADD(Dates[Dates Reserv]; -363;DAY )
Var dif reserv=[Num of dif reserv]-[Num of dif reserv same day LY]
This works until I introduce AGENCY column in the report. Then contiguos error apears in [Num of dif reserv same day LY] DATEADD function.
So I tried instead to avoid the error:
Num of dif reserv same day LY= sumx(calculate([Num of dif reserv];DATEADD(Dates[Dates Reserv]; -363;DAY ))
-->This returns a larger number which is not the right result "792"
if(HASONEVALUE(Dates[Dates Reserv]);calculate([Num of dif reserv];DATEADD(Dates[Dates reserv]; -363;DAY ));0)
-->In the table report mode I see each individual variation for each day but it returns "0" as result of total variation which is not right.
Is there a way I can get the same result I was getting with my original formulas but being able to segment this for each agency?
I would apreciate your help very much!
Thank you,
Marc
Hi @marcgotzens
I think your question is very similar to this topic for which I suggested a solution based on Power Query !
Let us know how it works for you.
Dear Excelside,
Thank you very much for your reply.
I took a look at your solved topic but I am new at power bi and not clear at how to apply that to my particular case with power query.
All I would like to know how many reservations for each agency in a date axis, and be able to visualize Drilling up and expanding down in the date hieracy (year, quarter, month). It will not be a problem as long as the dateadd function doesn't crash.
Thank you,
Marc
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |