MTD LY using Month as Filter Context

[Anonymous]

Hi,

I want to use month as filter conext for my table in power pivot and compare MTD sales TY vs MTD sales last year. For example today is Feb 8 and I want to show MTD sales for this year (currently using SUM(sales) and month as the filter context as my calendar table updates only to the max sales date). Where I am running into issues is MTD LY as it is totaling the whole month of last year (not to Feb 8, 2019). I do not want to put individual dates on the table to solve for this. Any help would be appreciated. 

Thanks!

[Anonymous · Accepted solution]

Hi @Anonymous ,

Please use the below formula :

SAME PERIOD LAST YEAR  = 
VAR DataMaxDate =
    CALCULATE ( MAX ( 'Table'[Date.Date].[Date] ), ALL ('Table' ) )
RETURN
    CALCULATE (
        [YTD Sales],
        SAMEPERIODLASTYEAR (
            INTERSECT (
                VALUES ('Table'[Date.Date].[Date] ),
                DATESBETWEEN ( 'Table'[Date.Date], BLANK (), DataMaxDate )
            )
        )
    )

NOTE : make sure to change Date column to date type otherwise you will end up with the same issue you are facing.

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.

Regards,

Gaurav Raj Singh

LinkedIN : https://www.linkedin.com/in/gauravrajsingh/

[Anonymous]

Thanks! That worked!

[amitchandak]

Try a filter of day

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last year MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)), day('Date'[Date])<=day(today()))

I have not tested it.

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on Linkedin

[MattAllington]

Yes, that's how it works and yes it can be frustrating. The month in your filter is actually using the last day of the month in the filter context (from the calendar table) hence the problem. You need a way to detect the last date with sales in the current month. I like to do this with a calculated column in the calendar table. You could call it something like "Past Date" and return true or false. If you then filter on this column, the calendar filter context will crab the last date with sales, and that should fix the last year data.