CODEWITHSUNDEEP
datepowerpivotdaxssas-tabular
BIDeveloper
BIDeveloper

Reputation: 847

Previous Year YTD till Same Date

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,

Upvotes: 0

Views: 473

Answers (1)

BIDeveloper
BIDeveloper

Reputation: 847

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

Upvotes: 0

Related Questions