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

Percentage difference in sales between months but till the day of today

I want to find the difference in sales between months but till the day of today. I wrote the measures below. Sales_till_day measure works. But when I want to find percentage differences, it doesn’t work, however, any error doesn't appear.

sales_till_day = CALCULATE([total sales];FILTER(Table;DAY('Table'[Date])<=DAY([today]));DATESINPERIOD('Table[Date];STARTOFMONTH('Table'[Date]);DAY([today]);DAY))

 

sales_till_day% =

VAR monthesbefore = CALCULATE(([sales_till_day]);FILTER('Table';MONTH('Table'[Date])<MONTH[today]))

VAR currentmonth = CALCULATE(([sales_till_day]);FILTER('Table';MONTH('Table'[Date])=MONTH[today]))

return

currentmonth/ monthesbefore

 

After the measures above, I tried to write different DAX with the name MTD. But the result was the same as previous

 

MTD= CALCULATE(TOTALMTD([total sales];'Table'[Date];FILTER('Table';Day('Table' [Date])<=day([today]))))

 

mtd% =

VAR monthesbefore = CALCULATE(TOTALMTD([total sales]);'Table'[Date];FILTER('Table';Day('Table' [Date])<=day([today])); FILTER('Table';MONTH('Table'[Date])<MONTH[today]))
VAR currentmonth = CALCULATE(TOTALMTD([total sales]);'Table'[Date];FILTER('Table';Day('Table' [Date])<=day([today])); FILTER('Table';MONTH('Table'[Date])=MONTH[today]))

Even I tried this

VAR MTD= CALCULATE(TOTALMTD([total sales];'Table'[Date];FILTER('Table';Day('Table' [Date])<=day([today]))))
return

IF([MTD]=0;0;DIVIDE(CALCULATE([MTD];'Table'[Date].[Year]=2019);[mtd])-1)

But these all do not help. If you have any suggestion please help with it.

6 REPLIES 6
Super User
Super User

Re: Percentage difference in sales between months but till the day of today

Hi @Vusal_Ahmadov 

 

Have you tried DATEADD like below.

Sales Previous Month = 
DIVIDE(
    [Sales],
    CALCULATE(
        [Sales],
        DATEADD( 'Calendar'[Date], -1, MONTH ) 
    )
)

Regards,
Mariusz

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

Vusal_Ahmadov Frequent Visitor
Frequent Visitor

Re: Percentage difference in sales between months but till the day of today

Thanks for your reply @Mariusz. This is just for one month. With the DAX you wrote I have to write a formula for 12 months. I need each month until the current day. For example, Today is the 21st of June.  
I need to see the sales till January 21, February 21, March 21 and so on. And the percentage comparison between June and other months. For example, June sales are 23% higher than January, 3% lower than February, etc.

Super User
Super User

Re: Percentage difference in sales between months but till the day of today

Hi @Vusal_Ahmadov 

Try the below.

VAR _day = DAY( TODAY() )
VAR _salesPrevousMonth = 
    CALCULATE(
        [Sales],
        FILTER( 
            DATEADD( 'Calendar'[Date], -1, MONTH ),
            DAY('Calendar'[Date]) <= _day
        )
    ) 
VAR _salesCurrentMonth = 
    CALCULATE(
        [Sales],
        FILTER( 
            'Calendar',
            DAY( 'Calendar'[Date] ) <= _day
        )
    )          
RETURN 
    DIVIDE( _salesCurrentMonth, _salesPrevousMonth )


Regards,
Mariusz

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

Vusal_Ahmadov Frequent Visitor
Frequent Visitor

Re: Percentage difference in sales between months but till the day of today

Hi  @Mariusz  , This happened:

error.PNG

Super User
Super User

Re: Percentage difference in sales between months but till the day of today

Hi @Vusal_Ahmadov 

DATEADD is a time intelligence function therefore it requires Date Dimension marked as date table.

Have you got one? 
Only asking as the error suggest that you have duplicates and or gaps in your dates, witch shoud not happend if you are using date table.

Hope this helps
Mariusz

Vusal_Ahmadov Frequent Visitor
Frequent Visitor

Re: Percentage difference in sales between months but till the day of today

Yes, there is date column in the table and there are no gaps. But there are duplicate dates because in one day there are several sales. The date is in date format.

Table is like ,

Date              Customer name      Sale value
20.06.2019    Customer 1             100
20.06.2019    Customer 2             70
21.06.2019    Customer 2             92
21.06.2019    Customer 12           105
21.06.2019    Customer 4             67

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 111 members 1,504 guests
Please welcome our newest community members: