Last month same period not computing correctly

[monojchakrab]

Hiya all,

I am trying to compute the current month MTD sales and compare the same with the previous month same period. The code for both are as below :

1. MTD :

MTD sales = 
CALCULATE(
    'Measures tray'[Total Sales],DATESMTD('Date Table'[Date])
    )

2. MTD M-1 :

MTD M-1 = 
CALCULATE('Measures tray'[MTD sales],
DATEADD(DATESMTD('Date Table'[Date]),-1,MONTH)

)

Problem is that while the MTD sales are computing correctly, the previous month is computing for the whole month. Screengrab below :

I then checked the table which the DATEADD function is returning and to my surprise, instead of returning the extact same dates as current month (DATESMTD) scaled back by a month, it is actually returning the full month of Jun (Vs. 01-22 of Jul). 

I dont think that is how it is supposed to work, right? or is there some other function I can work with to return the same range of dates, but with a month back?

I have gone thru some of the hacks on the web and I found that the codes they suggested are exactly like the one I have written.

Any help appreciated

[v-yiruan-msft · Accepted solution]

Hi @monojchakrab ,

You can refer the following links to get the same period sales of last month:

Dax Same period last months

1-MonthSamePeriod =
CALCULATE (
    SUM ( 'Table'[Sales] ),
    FILTER (
        'Table',
        AND (
            [Date]
                >= DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) - 1, 1 ),
            [Date]
                <= DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) - 1, DAY ( TODAY () ) - 1 )
        )
    )
)

---

Same Period Last Month

Prev MTD =
CALCULATE (
    SUM ( Sales[Sales_Amount] ),
    FILTER (
        ALL ( Sales[Sale_Date] ),
        Sales[Sale_Date]
            >= DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) - 1, 1 )
            && Sales[Sale_Date] <= TODAY ()
    )
)

How to compare SAME PERIOD LAST MONTH in Power BI using DAX

Best Regards

[monojchakrab]

This is very helpful @v-yiruan-msft 

[SpartaBI]

@monojchakrab in order to use Time Intelligence functions you need to have a proper data table that has dates from the start to the end of the year.
If you will have this table and used the date column in it you will get the result you need.
You will probably need to create a new column that has a flag of past future and add it to the filtet pane to only show past.
Read this artilcle for full explanation:
https://www.daxpatterns.com/standard-time-related-calculations/

If you need more help you can PM me and we could do a quick zoom.

      

Showcase Report – Contoso By SpartaBI

[monojchakrab]

@SpartaBI ,

My dates are all from a separate date table...this is not the problem as otherwise MTD and DATESMTD would not be returning the correct table/results

We can definitely have a zoom call when convenient for you. I am in India so you can check out the time difference with your location.

[SpartaBI]

@monojchakrab you can pm now with a link to zoom