Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.