Same Day Last Month Comparison

[Anonymous]

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

[Anonymous]

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

[v-yuta-msft · Accepted solution]

@Anonymous ,

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.

[v-yuta-msft]

@Anonymous ,

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.

[Greg_Deckler]

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.