Hello Everyone,
I have been struggling for the last fews days trying to sum numbers of night spent per reservation number.
Let me try to be the more precise on my explanation.
here is my data
I am trying to count the exact number of night spent between filtered date.
For example :
I have a total night spent for reservation A in room 100 of 32 nights. But If I put a filter date of dates between 01.01.2018 and 31.01.2018 , I have 30 nights.
So I have created the following measure to dynamically calculate my min date and max date based on my date filters.
var minday=
CALCULATE(
DATEVALUE(MIN('Reservations-Mews'[Column1.StartUtc]));all('Calendar');FILTER('Reservations-Mews';DATEVALUE('Reservations-Mews'[Column1.StartUtc])<=DATEVALUE(LASTDATE('Calendar'[Date]))&& DATEVALUE('Reservations-Mews'[Column1.EndUtc])>= DATEVALUE(FIRSTDATE('Calendar'[Date]))))
return
var maxday=
CALCULATE(
DATEVALUE(max('Reservations-Mews'[Column1.EndUtc]));all('Calendar');FILTER('Reservations-Mews';DATEVALUE('Reservations-Mews'[Column1.StartUtc])<=LASTDATE('Calendar'[Date])&& DATEVALUE('Reservations-Mews'[Column1.EndUtc])>= FIRSTDATE('Calendar'[Date])))
return
Then I have calculated the amounts of night spent within the filtered date , and I have created this measure to have it dynamically:
var days_passed=
CALCULATE(
DATEDIFF(
if(
DATEVALUE(minday)<DATEVALUE(FIRSTDATE('Calendar'[Date]));
DATEVALUE(FIRSTDATE('Calendar'[Date]));if(
DATEVALUE(minday)=DATEVALUE(LASTDATE('Calendar'[Date]));DATEVALUE(LASTDATE('Calendar'[Date]));DATEVALUE(minday)));
if(
DATEVALUE(maxday)>DATEVALUE(LASTDATE('Calendar'[Date]));
DATEVALUE(LASTDATE('Calendar'[Date]))+1;if(
DATEVALUE(maxday)=DATEVALUE(FIRSTDATE('Calendar'[Date]));DATEVALUE(FIRSTDATE('Calendar'[Date]));DATEVALUE(maxday)))
;DAY))
return
Until that point, everyhting is perfect.....but this is when a simple things get's so complicated that I need your help please!!!!!!!
Instead of summing all the nights spend per room, it is applying the rule of getting the min date during the period and the max date during periode .
Example: Date filter is between 01.01.2018 (included) and 31.01.2018(included)
Total of nights spent for reservation A :
Room 100 has 30 nights and room 101 has 6 nights.
So total of nights spent should be 36
But instead it is showing 30 nights, because the min date of reservation A is 01.01.2018 and max date of reservation A is 02.02.2018 ( corrected to 31.01.2018, due to my measure called minday), and the DATEDIFF between this two dates is 30 days.
So I have went trough all bunch of topics , that talks about SUMMARIZE, GROUPBY, SUMX.....but nothing works!!!! When I try Sumx it multiplies everything even the wrong total. Please I need your help
