cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Helper II

## You are X days into the Y

Given the following schema

Table: Calendar

• Date
• Week Number
• Fortnight Number
• Month Number

• 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).

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

3 REPLIES 3
Employee

@Ormesome wrote:

Given the following schema

Table: Calendar

• Date
• Week Number
• Fortnight Number
• Month Number

• 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?

Helper II

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
Not applicable

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

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Fabric Community Update - April 2024

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

#### Power BI Monthly Update - March 2024

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

Top Solution Authors
Top Kudoed Authors