A chart with 2 date columns

[aj1973]

Hi,

I have a table with 2 columns of dates(arrival dates and departure dates) and a measure.

The arrival date has to be <= Today()

The departure date has to be in the next 6 months and not including today

The measure is to calculate number of rooms occupied every day(I used the 2 filters of arrival date and deparutre date to calaculate).

The question is : how to make a chart Graph to see the measure of every day/month in the future? only one DATE on AXIS

Thank you 

[v-jiascu-msft]

Hi @aj1973,

We can create a date table and create two relationships between it and the table. Then create a measure with function USEREALTIONSHIP. Please share a dummy sample. We can build a solution.

Best Regards,

Dale

[aj1973]

hi Dale

here is the situation: I have a table where there are 3 columns  , the table represent reservations in a Camp. my aim is to determine the number of reservations in the future by day.

What i did is : 

Number of Reservation = (DISTINCTCOUNT(V_Reservations[OpReserv_ID]) then i used some filters

Reservations sold = CALCULATE([Number of Reservation];V_Reservations[Reserv_Statut]=6 || V_Reservations[Reserv_Statut]=8)

Forecast = CALCULATE([Reservations sold] ; V_Reservations[Reserv_DateArr] <= TODAY();V_Reservations[Reserv_DateDep] > TODAY())

Up till here the results are good.

what i need to have is the measure"Forecast" that is correct today, i want to see it correct tomorrow and all the future days. the result could be in a different column and in a different table that i already created   .

by the way I tried the  USEREALTIONSHIP and it did not work.

Can you help?

Regards

Amine

[v-jiascu-msft]

Hi Amine,

Can you provide a dummy sample?

Best Regards,

Dale

[aj1973]

[v-jiascu-msft]

Hi @aj1973,

Please provide the original-like data. You can create a dummy. 

Maybe you can check out the demo in the attachment. You can modify it to the scenario of yours. 

1. Delete the relationship between Date table and the fact table. Or create a new independent Date table.

2. Create a measure like below.

RoomOccupied =
IF (
    MIN ( 'Calendar'[Date] ) < TODAY (),
    BLANK (),
    CALCULATE (
        DISTINCTCOUNT ( Table1[OpReserv_ID] ),
        FILTER (
            'Table1',
            'Table1'[Reserv_DateArr] <= MIN ( 'Calendar'[Date] )
                && 'Table1'[Resrv_DateDep] >= MIN ( 'Calendar'[Date] )
                && 'Table1'[Reserv_DateArr] <= TODAY ()
                && 'Table1'[Resrv_DateDep] > TODAY ()
        )
    )
)

Best Regards,

Dale

[aj1973]

Hi Dale,

Sorry for not replying yesterday (did not go to work).

Your solution worked fine but with a slight Time Lag between results:

• here is how it should be 

• and here is what i got out of your code

Don't know if i should bother you again but i will try to figure it out.

Thank you very much

[v-jiascu-msft]

Hi @aj1973,

Please reproduce the issue based on the demo I shared with you. Or share your data. I tried but can't find out what's going wrong from the snapshots.

Best Regards,

Dale

[aj1973]

Hi Dale,

Thank you for your patience. here I share with you the Data in Dummy.pbix. I also joined some Snapshot of the data in our Application. 

https://drive.google.com/file/d/1hCehpjeocUghhGcGRAwYXaMaupCwsbbs/view?usp=sharing

Some snapshots

https://drive.google.com/drive/folders/1pSjWJX3SNCOl7SHVHoCtAMXcq_WrnAaQ?usp=sharing

Today, to the contrary of yesterday i found out that the numbers in the visual of Rooms Checked IN  are coming out for some Sites( Site ID= 8 , 14, and 13) incorrect and for the Site ID=11 "correct" but of course with a Time lag of one day.

For the visual Rooms sold the numbers are coming out correct but with a Time lag of one day

let me know if you needed more details

Thanks a lot

[v-jiascu-msft · Accepted solution]

Hi @aj1973,

Try this formula, please. It's a data type issue. It's DateTime in details while it's Date in the date table. So the calculation range is wrong.

Room_Occupied 2 =
IF (
    MIN ( 'Calendar'[Date] ) < TODAY (),
    BLANK (),
    CALCULATE (
        DISTINCTCOUNT ( V_Reservations_Amine[OpReserv_ID] ),
        FILTER (
            'V_Reservations_Amine',
            'V_Reservations_Amine'[Reserv_DateArr].[Date] <= MIN ( 'Calendar'[Date] )
                && 'V_Reservations_Amine'[Reserv_DateDep].[Date] > MIN ( 'Calendar'[Date] )
                && 'V_Reservations_Amine'[Reserv_DateArr].[Date] <= TODAY ()
                && 'V_Reservations_Amine'[Reserv_DateDep].[Date] > TODAY ()
        )
    )
)

Best Regards,

Dale

[aj1973]

Perfect.

Thak you very much Dale

Amine

[v-jiascu-msft]

It's my pleasure, Amine. Could you please mark my answer as a solution?

Best Regards,

Dale