cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

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

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

1 ACCEPTED SOLUTION
Super User

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

``````Measure =
VAR __Calendar =
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)``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
15 REPLIES 15
Super User

@mheah So like the attached file (below sig)

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Helper I

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

Super User

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

``````Measure =
VAR __Calendar =
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)``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Helper I

Thank you @Greg_Deckler

Can i ask how come do you use

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

``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

Super User

@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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Helper I

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.

Super User

@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.
)``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Helper I

Thanks @Greg_Deckler

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

Helper I

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.

Super User

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper I

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.

Super User

Hi,

Please share some more data to work with.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Community Support

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.

Super User

@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

Super User

@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

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.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors