Previous Year YTD till Same Date

0
votes

I have requirement, Previous Year YTD till the same date as YTD is returing results for example if the Records in my table is present from 1-jan-2016 to 9-May-2016 then this year YTD will Calculate till 9-May-2016 (this is Working fine ) and Previous year YTD also should calculate till 9-May-2015 from 1-Jan-2015 not for whole year or whole month

I had tried all the solutions mentioned here :- DAX Pattern

MSDN

But didnt get any result yet,

1
datepowerpivotdaxssas-tabular
Can you post the formulas you are using. The problem is probably that you are selecting a whole month in your selection. So YTD will take into account the full month. You can avoid this behaviour by using the function lastnonblank() function. – WimV
@WimV I tried with last non blank too, here is the formula Revenue YTD PY:=CALCULATE([Revenue],LASTNONBLANK('Date Master'[Date],[Revenue]),DATESBETWEEN('Date Master'[Date], FIRSTDATE(DATEADD('Date Master'[Date],-12,MONTH)), LASTDATE(DATEADD('Sales Details'[InvoiceDate2],-12,MONTH)))) – Piyush Jain

1 Answers

0
votes

After trying so many solutions finally got the solutions which solves my purpose.

In my fact table I added a column named Invoicedate and format is "YYYYMMDD" the similar column in Date table names as Datekey and fomrat is "YYYYMMDD".

Here is the calculation for YTD:

Revenue YTD:=CALCULATE([Revenue],DATESYTD('Date Master'[Date],"03-31"),ALL('Date Master'))

And the Here is the calculation for Previous Year YTD:-

Revenue PY:=CALCULATE([Revenue YTD],FILTER(ALL('Date Master'[Datekey]),FILTER(VALUES('Sales Details'[InvoiceDate]),'Sales Details'[InvoiceDate]-10000=EARLIER('Date Master'[Datekey]))),ALL('Date Master'))

Benefit of this solution is, it handles the leap year issue well. I got the idea of this solution from :- Chris Webb's BI Blog