March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe 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
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.
Solved! Go to Solution.
hello @Anonymous,
Possibly this could be your solution:
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)
Proud to be a Super User!
hello @Anonymous,
Possibly this could be your solution:
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)
Proud to be a Super User!
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
93 | |
89 | |
86 | |
77 | |
49 |
User | Count |
---|---|
164 | |
149 | |
101 | |
73 | |
56 |