cancel
Showing results for
Search instead for
Did you mean:
Highlighted
Frequent Visitor

## Same Day Last Month Comparison

Hi

I have a figure that I count for the month and I want to create a field that will show me the count compared to the same day last month.

I am using direct query so some of the suggestions I have already seen don't work with it.

Any advice would be much appreciated.

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

## Re: Same Day Last Month Comparison

You could modify the measures like pattern below:

```Total Sales Diff =
VAR First_Day_Current_Month =
EOMONTH ( Table[Date], -1 ) + 1
VAR Third_Day_Current_Month =
EOMONTH ( Table[Date], -1 ) + 3
VAR First_Day_Last_Month =
EOMONTH ( Table[Date], -2 ) + 1
VAR Third_Day_Last_Month =
EOMONTH ( Table[Date], -2 ) + 3
VAR Total_Sales_Current_Month =
CALCULATE (
SUM ( Table[Sales] ),
FILTER (
Table,
Table[Date] >= First_Day_Current_Month
&& Table[Date] <= Third_Day_Current_Month
)
)
VAR Total_Sales_Last_Month =
CALCULATE (
SUM ( Table[Sales] ),
FILTER (
Table,
Table[Date] >= First_Day_Last_Month
&& Table[Date] <= Third_Day_Last_Month
)
)
RETURN
Total_Sales_Current_Month - Total_Sales_Last_Month
```

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 REPLIES 4
Super User

## Re: Same Day Last Month Comparison

Sample data and expected output would help. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490.

That being said, can you calculate the date like:

`Measure = DATE(YEAR([Date]),MONTH([Date])-1,DAY([Date]))`

Something along those lines? Obviously will have to account for January dialing back to December of last year but you could handle that with an IF statement or SWITCH.

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Community Support Team

## Re: Same Day Last Month Comparison

So suppose you want to achieve sales in same day last month, you can create a measure using DAX like pattern below:

```Total Sales =
VAR Same_Day_Last_Month =
EDATE ( Table[Date], -1 )
RETURN
CALCULATE (
SUM ( Table[Sales] ),
FILTER ( Table, Table[Date] = Same_Day_Last_Month )
)
```

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Frequent Visitor

## Re: Same Day Last Month Comparison

So a very simple example would be if I had a list of dates and a count beside them:

01/01/2019 - 10

02/01/2019 - 20

03/01/2019 - 70

.

.

.

.

01/02/2019 - 20

02/02/2019 - 40

03/02/2019 - 100

So far in February I have 160 and I want to compare that to the first 3 days in january. So my calculation would tell me that I am +60 as there have been 60 more in feb than jan for the same time period.

That is the kind of calculation I am looking for.

Thanks

Community Support Team

## Re: Same Day Last Month Comparison

You could modify the measures like pattern below:

```Total Sales Diff =
VAR First_Day_Current_Month =
EOMONTH ( Table[Date], -1 ) + 1
VAR Third_Day_Current_Month =
EOMONTH ( Table[Date], -1 ) + 3
VAR First_Day_Last_Month =
EOMONTH ( Table[Date], -2 ) + 1
VAR Third_Day_Last_Month =
EOMONTH ( Table[Date], -2 ) + 3
VAR Total_Sales_Current_Month =
CALCULATE (
SUM ( Table[Sales] ),
FILTER (
Table,
Table[Date] >= First_Day_Current_Month
&& Table[Date] <= Third_Day_Current_Month
)
)
VAR Total_Sales_Last_Month =
CALCULATE (
SUM ( Table[Sales] ),
FILTER (
Table,
Table[Date] >= First_Day_Last_Month
&& Table[Date] <= Third_Day_Last_Month
)
)
RETURN
Total_Sales_Current_Month - Total_Sales_Last_Month
```

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.