You are X days into the Y

[Ormesome]

Given the following schema

Table: Calendar

• Date
• Week Number
• Fortnight Number
• Month Number

Table: GoogleAnalytics

• Date
• Web Page
• Number Of Pageviews

where the 2 tables join on the Date fields,

How do I display

• "you are 2 days into the week"?
• "you are 9 days into the fortnight"?
• "you are 31 days into the month"?

I've also been asked to divide the year into other arbitrary time periods (lunar calendar, for example).

[Eric_Zhang]

---

@Ormesome wrote:

Given the following schema

Table: Calendar

• Date
• Week Number
• Fortnight Number
• Month Number

Table: GoogleAnalytics

• Date
• Web Page
• Number Of Pageviews

where the 2 tables join on the Date fields,

How do I display

• "you are 2 days into the week"?
• "you are 9 days into the fortnight"?
• "you are 31 days into the month"?

I've also been asked to divide the year into other arbitrary time periods (lunar calendar, for example).

---

@Ormesome

Your questions are not quite clear for me, could you post any sample data and the expected output?

[Ormesome]

Because the 2 tables are joined there is an implicit context filter on the calendar table. I found it tricky to use DAX to calculate the number of days that have passed this fortnight when there is no data in the GA table for the first 2 days.

I have reconsidered the problem and have successfully calculated this in Power Query.

Thank you

[Anonymous · Accepted solution]

Date math is going to be your best solution here.  Using that you can do things like:

• Take the Day Of the Week number, adjust it for your starting day, and then build a string using a measure to display the result
• Take the Date and extract the 'Day value', and then build a string using a measure to display the result
• Take the week number, check if its divisible by 2 (i.e. even or odd).  This tells you which side of the fortnight you are on, so you can produce a different number to when you are on the other side of the fortnight.

You can use measures within measures, so in your fortnight calculation, you can use the result of your weekly calculation.  Once you have all the numbers you need, you can create the Text display measures that look something like

Display Week = "You are " & [WeekAmount] & " days into the week."