Calculating Month to Date, Last Week, and Year to Date

[Anonymous]

I need to do a calculation like the chart below based on the date in my table broken out by source. My data set looks like this:

Cust_ID	Source	Date
9	Source 1	10/1/2020
8	Source 2	9/21/2020
5	Source 3	10/28/2020
3	Source 1	10/1/2020
2	Source 1	9/21/2020
1	Source 2	10/28/2020
3	Source 1	10/1/2020
4	Source 2	9/21/2020
6	Source 1	10/28/2020

What formula can I use to portray that in PowerBI?

[v-rzhou-msft]

Hi @Anonymous 

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your problem or share me with your pbix file from your Onedrive for Business.

Best Regards,

Rico Zhou

[v-rzhou-msft]

Hello @jnguyen605

I think you want to count the number of identifiers for each source for the last week, mtd and ytd.

I use your sample, add some values and build three measures to achieve your goal.

First, we have to build a date table.

Date = ADDCOLUMNS(CALENDARAUTO(),"Year",YEAR([Date]),"Month",MONTH([Date]),"Day",DAY([Date]),"Weeknum",WEEKNUM([Date],2))

Here I use weeknum (date,2), which will start a week for Monday, if you want to start a week for Sunday you can change 2 to 1.

Measures:

LastWeek = 
VAR _CurrentWeeknum = CALCULATE(SUM('Date'[Weeknum]),FILTER('Date','Date'[Date] = TODAY()))
VAR _DateTable = CALCULATETABLE(VALUES('Date'[Date]),FILTER('Date','Date'[Weeknum]=_CurrentWeeknum-1))
VAR _Count = CALCULATE(COUNT('Table'[Cust_ID]),FILTER('Table','Table'[Date] in _DateTable))
Return
IF(_Count = BLANK(),0,_Count)

Month to Date = 
VAR _FirstDaythismonth = EOMONTH(TODAY(),-1)+1
VAR _COUNT = CALCULATE(COUNT('Table'[Cust_ID]),FILTER('Table','Table'[Date]<TODAY()&&'Table'[Date]>=_FirstDaythismonth))
Return
IF(_COUNT=BLANK(),0,_COUNT)

Year to Date = 
VAR _Firstdaythisyear = MINX(FILTER('Date','Date'[Year]= YEAR(TODAY())),'Date'[Date])
VAR _Count = CALCULATE(COUNT('Table'[Cust_ID]),FILTER('Table','Table'[Date]<TODAY()&&'Table'[Date]>=_Firstdaythisyear))
Return
IF(_Count =BLANK(),0,_Count)

The result is as follows.

You can download the pbix file from this link: Month-to-Date, Last Week, and Year-to-Date Calculation

Best regards

Rico Zhou

If this post helps,then consider Accepting it as the solution to help other members find it faster.

[amitchandak]

@Anonymous , With help from date table you can do that.

Examples

Week

New columns in date table 
Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)	

Measures 
This Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))
Last year Week= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=(max('Date'[Week Rank]) -52)))
Last 8 weeks = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-8 && 'Date'[Week Rank]<=max('Date'[Week Rank])))
last two weeks = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]<=max('Date'[Week Rank])-1

Month

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
this month =MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH('Date'[Date])))
last MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
previous month value =  CALCULATE(sum(''Table''[total hours value]),previousmonth('Date'[Date]))

diff = [MTD Sales]-[last MTD Sales]
diff % = divide([MTD Sales]-[last MTD Sales],[last MTD Sales])

Year

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

Refer my links

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
Power BI — Qtr on Qtr with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
Day Intelligence - Last day, last non continous day
https://medium.com/@amitchandak.1978/power-bi-day-intelligence-questions-time-intelligence-5-5-5c3243d1f9

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

Please provide your feedback comments and advice for new videos
Tutorial Series Dax Vs SQL Direct Query PBI Tips
Appreciate your Kudos.