How to create custom buckets of dates

[Anonymous]

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. 

[ChrisMendoza · Accepted solution]

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)