Get Month to Date, same days not dates

[etiennemichelet]

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")
)

[v-diye-msft]

Hi @etiennemichelet 

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:

https://community.powerbi.com/t5/Desktop/Prior-Year-Sales-Filter-Retail-comparison/m-p/935825#M44832... 

[amitchandak]

@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-La...

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))))