Compare MTD to same period last year

[ciken]

Trying to build out a MTD table that will show me how I am doing through the current day in the current month vs the exact same time frame last year (i.e. August 1-10, 2020 vs August 1-10, 2019). When I write my DAX, no matter how many different ways I try, I end up with the full month of August 2019 in comparison and I cannot figure out how to make it only show me the specific dates I'm looking for.  I am positive (as you'll see below) my company did not seel $1m in revenue during the first 10 day so August 2019. Below are my DAX formulas and a screen shot of my table. I've tried SAMEPERIODLASTYEAR which also does not work. Any thoughts are appreciated!!

Thanks for the help!

MTD Sales = CALCULATE([Total Revenue],DATESMTD('Calendar'[Date]))

SALES LY = CALCULATE([Total Revenue],DATEADD('Calendar'[Date],-1,YEAR))

MTD Last Year = CALCULATE([SALES LY],DATESMTD('Calendar'[Date]))

 

[amitchandak]

@ciken , Try like

MTD QTY forced= 
var _max = today()
return
calculate(Sum('order'[Qty]),DATESMTD('Date'[Date]),'Date'[Date]<=_max)


LMTD QTY forced= 
var _max = date(year(today())-1,month(today()),day(today()))
return
CALCULATE(Sum('order'[Qty]),DATESMTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max)

This happen when you view by month year, for last year it gets the month end date.

[parry2k · Accepted solution]

@ciken sorry here is the measure for the last year, using the existing measure I shared with you

Revenue MTD PY = 
VAR __today = TODAY()
VAR __lastYear = EDATE ( __today, -12 )
RETURN
CALCULATE (
    [Revenue MTD],
    SAMEPERIODLASTYEAR ( 'Dim Calendar'[Date] ),
    'Dim Calendar'[Date] <= __lastYear
)

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Greg_Deckler]

You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000

Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

[parry2k]

@ciken try following measure

Revenue MTD = 
CALCULATE ( 
    [Total Revenue], 
    DATESMTD ( 
        CALCULATETABLE ( 
            VALUES ( 'Calendar'[Date] ), 
            KEEPFILTERS ( 'Calendar'[Date] <= TODAY() ) 
        )
    ) 
)

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[ciken]

Thanks @parry2k , This gave me the MTD for THIS YEAR but not last year.