cancel
Showing results 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

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]

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

2 REPLIES 2
MVP

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.

New Member

Dear Excelside,

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

Announcements

#### 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.

#### Power BI Monthly Update - February 2024

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

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

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

Top Solution Authors
Top Kudoed Authors