October 10, 2014 at 3:06 am
Hi
I am trying to add/calculate a YTD column in my data.
the end result should allow me to have a current month to date in one column and a ytd in the next column.
I am (very) new to DAX and am clearly missing something.
I have used this to create a new measure in the pivot table...
CALCULATE(SUM('Fact Sales'[InvoiceValue]), DATESYTD('Dim Dates'[Date]))
but the result is not correct. i get..
Product.....Jan MTD....Jan YTD....Feb MTD....Feb YTD....Mar MTD....Mar YTD
Prod12......5000.........5000........2500.........2500........3000.........3000
Prod23......1000.........1000........2000.........2000........1000.........1000
i am trying to achieve this...
Product.....Jan MTD....Jan YTD....Feb MTD....Feb YTD....Mar MTD....Mar YTD
Prod12......5000.........5000........2500.........7500........3000.........10500
Prod23......1000.........1000........2000.........3000........1000.........4000
can anyone help?
October 10, 2014 at 3:34 am
solved it....
=CALCULATE(SUM('Fact Proteus Sales'[InvoiceValue]), DATESYTD('Dim Dates'[Date]),ALL('Dim Dates'))
many thanks to this vid...
https://www.youtube.com/watch?v=E-3TST8uLSk
🙂
October 10, 2014 at 3:47 am
Glad you found it. Thanks for posting the solution.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 10, 2014 at 5:04 am
and just to expand on this a little further, here's the calculation to do the YTD for the same period the previous year....
=CALCULATE(SUM('Fact Proteus Sales'[InvoiceValue]), DATESYTD(SAMEPERIODLASTYEAR('Dim Dates'[Date])),ALL('Dim Dates'))
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply