Count of each 'day-of-week' between 2 dates

[mheah]

Hi

I wish to do find out the 'count' of each 'day-of-week' between 2 dates (Arrival Date; Departure Date)

Arrival Date	Departure Date	Room nights	1	2	3	4	5	6	7
Thursday, August 27, 2020	Friday, August 28, 2020	1				1

(1-7: referring to the DOTW. 4 being a Thursday)

In the above excel table, i used the formula 

=SUMPRODUCT(--WEEKDAY(ROW(INDIRECT("arrival_date"&":"&"departure_date"-1)),2)="4")) 

to find out which DOTW are they staying through.

How can i do the same over powerbi as i'm moving my workings all unto powerbi. 

Much appreciated

[Greg_Deckler]

@mheah So like the attached file (below sig)

[mheah]

Hi @Greg_Deckler , I think your solution is what i'm looking for. However one inaccuracy is that departure_date shouldn't be counted as 1 as there's no night stay on the departure date. What should i do to revise on your measure?

Also, i'm poor in DAX, if you can, explain to me how you workout the solution. Didn't quite understand the DAX workflow.

Thanks

[Greg_Deckler · Accepted solution]

@mheah That's easy, just subtract 1 from your max departure date then in your calendar table.

Measure = 
  VAR __Calendar = 
    ADDCOLUMNS(
      CALENDAR(
        MAX('sample list'[Arrival Date US]),
        MAX('sample list'[Departure Date US])-1
      ),
      "Weekday",WEEKDAY([Date],2)
    )
    VAR __Weekday = MAX('Table'[Weekdays])
RETURN
    IF(__Weekday IN SELECTCOLUMNS(__Calendar,"Weekday",[Weekday]),1,0)

[mheah]

Thank you @Greg_Deckler 

Can i ask how come do you use 

MAX('sample list'[Arrival Date US])

instead of 

Min('sample list'[Arrival Date US])

 ?

Isn't it suppose to list a whole list of dates starting from the earliest arrival date to the latest departure date?

Also, The total isn't updating at all

[Greg_Deckler]

@mheah It depends on context. If each row in the table visualization is a single record with 1 arrival and departure date then it doesn't matter if you use MAX or MIN because the aggregation is just there because we have to have one. But since it is a single record, it doesn't matter. But sure, you can replace the MAX with MIN, especially if you are dealing with more than one record that would probably be a good idea.

As for the totals. That is a measures totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

[mheah]

Hi @Greg_Deckler 

Thanks for your reference posts. Tried to comprehend it through but to no avail. Seem like i need another measure in order to sum up the measure?

Are you able to help me to build this measure for me? I need to sum up the number of weekdays from this whole list of 'confirmations', so that i can build a visual on it.

Greatly appreciate that.

[Greg_Deckler]

@mheah Correct, that's how the system works although it is possible to do it all in one measure, I find it easier to explain by using 2 measures. The issue is that it heavily depends on how your visual is laid out but it should be something along the lines of:

Measure 2 = 
  IF(
    HASONEVALUE('Table'[Confirmation]), //if this is one value, we are not in a total row
    [Measure], //if not in a total row, just return the measure for a single row
    SUMX(SUMMARIZE('Table',[Confirmation],"__Measure",[Measure]),[__Measure]) //if in a total row, summarize the table according to how that same summarization is happening in the visual and sum up the results of the original measure within that context.
  )

[mheah]

Thanks @Greg_Deckler 

it works very well as to what i need it to be.

[mheah]

Hi @Greg_Deckler @amitchandak @v-lionel-msft @Ashish_Mathur 

Thank you for your kind replies. Thanks @Greg_Deckler for providing with the forum guide posts, am still new to the community. Am trying to think how best i can describe my problem, please bear with me.

The end goal i have in mind is to tabulate out which day these guests are staying on? Given that i only have arrival_date & departure_date data from my system. (**Please note that departure_date  does not constitute to a night stay.)

image: (https://bit.ly/2QVvJ6t)

I am also providing a sample raw file so that you guys can see what kind of data that is available to get the analysis i need. Powerbi file (https://bit.ly/2GjJ8TF)

Need the correct measure in order to compute that analysis on powerbi.

Thank you once again.

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[mheah]

Hi @Ashish_Mathur 
Thanks, but i don't quite understand how you create the column "date" from? also, the "sample_list" is missing crucial data columns of "arrival_date" and "departure_date" in order to find out the period of stay. Thus i can't accept it as a accepted solution, maybe i am missing something from your help.

[Ashish_Mathur]

Hi,

Please share some more data to work with.

[v-lionel-msft]

Hi @mheah ,

Like this?

Please refer to my .pbix file.

If it's not what you want, please post the sample data table and the expected output table.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@mheah , Not very clear. You need to have weekday in your date table and then you can use one of the two approach

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

or refer this file

https://www.dropbox.com/s/bqbei7b8qbq5xez/leavebetweendates.pbix?dl=0

[Greg_Deckler]

@mheah - Take a look at the equivalent DAX functions/calculations here:

https://community.powerbi.com/t5/Community-Blog/Excel-to-DAX-Translation/ba-p/1060991

Otherwise, Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.