Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
Anonymous
Not applicable

How to create custom buckets of dates

I have been tasked by my management team to replicate reports they are currently using in Motivity. I cannot figure out how to create the following buckets.

 

Today
This week to date
Last Week to date
Last week total
This month to date
Last month to date
Last month Total


Im still trying to figure out Power BI and not sure how to either write these columns or if i need a seperate table etc. 

1 ACCEPTED SOLUTION
ChrisMendoza
Resident Rockstar
Resident Rockstar

hello @Anonymous,

 

Possibly this could be your solution:

 

  1.  Add columns to your dCalendar using the Query Editor. [Week of Year] & [Month] (where [Month] is monthnum
    1. I did not create a dCalendar for this example, and it still works as I expected
  2. Create your measure as below:

 

Sales Today = CALCULATE([Sales ($)],TableName[Date]=TODAY()) 
This Week to Date = 
VAR curWeek = LOOKUPVALUE(
    TableName[Week of Year],
    TableName[Date],TODAY()
)

VAR thisWeekToDate =CALCULATE([Sales ($)],TableName[Week of Year] = curWeek)

RETURN thisWeekToDate
Last Week Total = 
VAR curWeek = LOOKUPVALUE(
    TableName[Week of Year],
    TableName[Date],TODAY()
)

VAR lastWeek = CALCULATE([Sales ($)],TableName[Week of Year] = curWeek -1)

RETURN lastWeek

 

Last Week to Date = 
VAR curWeek = LOOKUPVALUE(
    TableName[Week of Year],
    TableName[Date],TODAY()
)

VAR lastWeek = curWeek - 1

VAR lastWeekToDate =CALCULATE([Sales ($)],TableName[Week of Year] = lastWeek)

RETURN lastWeekToDate + [This Week to Date]

 

This Month to Date = TOTALMTD([Sales ($)],TableName[Date],MONTH(TableName[Date]) = MONTH(TODAY()))
Last Month to Date = [This Month to Date]+[Last Month Total] 
Last Month Total = CALCULATE([Sales ($)],TableName[Month] = MONTH(TODAY())-1)

2.PNG






Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!



View solution in original post

1 REPLY 1
ChrisMendoza
Resident Rockstar
Resident Rockstar

hello @Anonymous,

 

Possibly this could be your solution:

 

  1.  Add columns to your dCalendar using the Query Editor. [Week of Year] & [Month] (where [Month] is monthnum
    1. I did not create a dCalendar for this example, and it still works as I expected
  2. Create your measure as below:

 

Sales Today = CALCULATE([Sales ($)],TableName[Date]=TODAY()) 
This Week to Date = 
VAR curWeek = LOOKUPVALUE(
    TableName[Week of Year],
    TableName[Date],TODAY()
)

VAR thisWeekToDate =CALCULATE([Sales ($)],TableName[Week of Year] = curWeek)

RETURN thisWeekToDate
Last Week Total = 
VAR curWeek = LOOKUPVALUE(
    TableName[Week of Year],
    TableName[Date],TODAY()
)

VAR lastWeek = CALCULATE([Sales ($)],TableName[Week of Year] = curWeek -1)

RETURN lastWeek

 

Last Week to Date = 
VAR curWeek = LOOKUPVALUE(
    TableName[Week of Year],
    TableName[Date],TODAY()
)

VAR lastWeek = curWeek - 1

VAR lastWeekToDate =CALCULATE([Sales ($)],TableName[Week of Year] = lastWeek)

RETURN lastWeekToDate + [This Week to Date]

 

This Month to Date = TOTALMTD([Sales ($)],TableName[Date],MONTH(TableName[Date]) = MONTH(TODAY()))
Last Month to Date = [This Month to Date]+[Last Month Total] 
Last Month Total = CALCULATE([Sales ($)],TableName[Month] = MONTH(TODAY())-1)

2.PNG






Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!



Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

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

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.