Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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

 

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

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

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

@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
Super User
Super User

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.