cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
VictoriaQ Frequent Visitor
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
Community Support Team

Re: Same Day Last Month Comparison

@VictoriaQ ,

 

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
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
Community Support Team

Re: Same Day Last Month Comparison

@VictoriaQ ,

 

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.

VictoriaQ Frequent Visitor
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
Community Support Team

Re: Same Day Last Month Comparison

@VictoriaQ ,

 

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.