Forum Discussion
Get Month to Date, same days not dates
Hi,
I'm looking to find a way to compare Month-to-Date, year-on-year but looking at the same days. So as of today (03/06/2020) I want to compare this with the period 03-05/06/2019 because that is the period starting with a Monday.
I had managed to add a column to my Calendar table and labeled pre and post which worked for a couple of months, but this month it does not, because my calculation was based on using last year weekday for the first day of the equivalent month, which here was higher than this year's. As a result the previous period starts a week early, the 27/05/2019.
Doe anyone either have a solution for this or an alternative I can use to get the periods to compare?
Thanks,
Here is my DAX (DimDate[Date] is a date column in my calendar table) :
IF(DimDate[Date] >=
DATE(YEAR(TODAY())-1,MONTH(TODAY()),1) +
(
WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),1),2)
-
WEEKDAY(DATE(YEAR(TODAY())-1,MONTH(TODAY()),1),2)
)
=
&&
DimDate[Date] <
DATE(YEAR(TODAY())-1,MONTH(TODAY()),1) +
(
WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),1),2)
-
WEEKDAY(DATE(YEAR(TODAY())-1,MONTH(TODAY()),1),2)
)
+
DAY(TODAY()-1)
,
"pre",
if(
MONTH(DimDate[Date]) = MONTH(TODAY()) && YEAR(DimDate[Date]) = YEAR(TODAY()) && DAY(DimDate[Date]) < DAY(TODAY()),
"post")
)
2 Replies
- amitchandak
Super User
etiennemichelet , You need WTD to MTD
For WTD refer https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
Using Rank, Week Number and Week day you can create new combination
For MTD You can
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 MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH)))) - v-diye-msft
Community Support
Your data is too scarce to repro. you might consider creating pbix file that will contain some sample data (remove the confidential info), upload the pbix to onedrive for business and share the link to the file. Please do not forget to describe the expected results based on this sample data.
Here's a similar post which might provide you some hints:
