cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
marcgotzens
New Member

Contiguous date solutions DATEADD with sumx and if(hasonevalue... functions

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]

 calculate.PNG

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"

 sumx.PNG

 

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.

 if(hasonevalue.PNG

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

 

2 REPLIES 2

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

 

 

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.