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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
monojchakrab
Resolver III
Resolver III

Last month same period not computing correctly

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 :

 

monojchakrab_0-1658636700897.png

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

 

monojchakrab_1-1658637663324.png

 

monojchakrab_2-1658637723510.png

 

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

1 ACCEPTED SOLUTION
v-yiruan-msft
Community Support
Community Support

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 )
        )
    )
)

yingyinr_1-1658915014213.png


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

Community Support Team _ Rena
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

5 REPLIES 5
v-yiruan-msft
Community Support
Community Support

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 )
        )
    )
)

yingyinr_1-1658915014213.png


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

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

This is very helpful @v-yiruan-msft 

SpartaBI
Community Champion
Community Champion

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



2022-05-19 17_30_22-Re_ Need help on DAX function with measure vs colu... - Microsoft Power BI Commu.png


Full-Logo11.png

SpartaBI_3-1652115470761.png   SpartaBI_1-1652115142093.png   SpartaBI_2-1652115154505.png

Showcase Report – Contoso By SpartaBI

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

@monojchakrab you can pm now with a link to zoom

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors