Skip to main content
cancel
Showing results for 
Search instead 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.

Reply
Ormesome
Helper II
Helper II

You are X days into the Y

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

 

 

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

 

View solution in original post

3 REPLIES 3
Eric_Zhang
Employee
Employee


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

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

 

Helpful resources

Announcements
Microsoft Fabric Learn Together

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

April Fabric Community Update

Fabric Community Update - April 2024

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

March 2024 PBI Gallery Image

Power BI Monthly Update - March 2024

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