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

[Anonymous]

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.

[Mariusz]

Hi @Anonymous 

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.

[Anonymous]

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.

[Mariusz]

Hi @Anonymous 

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.

[Anonymous]

Hi  @Mariusz  , This happened:

[Mariusz]

Hi @Anonymous 

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

[Anonymous]

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