YTD Weekly and Monthly not working

[Anonymous]

Hello everyone,

I'm having a problem with my "This month's" and "This week's" static numbers. I am trying to display one specific number that will be the same every week for a month. You could call it a "Goal". I have a week rank and month rank column in my calendar table, however, when I put the DAX I am using in a visual it is showing last month's number. For example, I want to show a headcount for this week/month, which usually doesn't change over a week or month, but it shows last month's number and if I use a slicer to filter to next month it shows what should be this months number. Any help on this would be greatly appreciated!

This is the DAX I've been currently using. When I use month rank it produces (blank)

This Month's Headcount = CALCULATE(sum('Monthly Information'[Headcount]), FILTER(ALL('Calendar'),'Calendar'[Week Rank]=max('Calendar'[Week Rank])))

[amitchandak]

@Anonymous , for month you can use datesmtd or month rank on month year

This Month's Headcount = CALCULATE(sum('Monthly Information'[Headcount]), FILTER(ALL('Calendar'),'Calendar'[Month Rank]=max('Calendar'[Month Rank])))

examples

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]))
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

new column

Month Rank = RANKX(all('Date'),'Date'[Month Start date],,ASC,Dense) //Yea month column /YYYYMM

This Month = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])))
Last Month = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])-1))

Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA

[Anonymous]

Both of these do not work. Any idea why?

[Anonymous · Accepted solution]

So, I figured out why it didn't work. In my calendar M code, I had this as my end date: EndDate = Date.EndOfDay(Today), when it should have been EndDate = Date.EndOfMonth(Today). If you have end of date today the "Month Rank" does not work properly. Also, if you don't want the future months showing up on visuals, I suggest in the M code to use Date.EndOfMonth. EndOfYear will populate all months in your visuals.👍