Urgently need assistance with YTD calculation

[anupampandey]

Hi,

I am using one of the function from previous post to calculate the YTD.

YTDValue =
VAR RowDate = RetailerSummary[Transaction Date]
RETURN
CALCULATE (SUM(RetailerSummary[Tran_Amount]),
FILTER(RetailerSummary,RetailerSummary[Transaction Date]<=RowDate
&&
YEAR(RetailerSummary[Transaction Date] = YEAR(RowDate))
))

But I am not able to filter it according to the financial year. My financial year begins from 1st Apr and End on 31st March.

Some times it says "The operation has been cancelled because there is not enough memory available for the application. If using a 32-bit version of the product, consider upgrading to the 64-bit version or increasing the amount of memory available on the machine."

Also there is no relationship between Date table and transaction fact table. In my transaction table on one perticular date fact table is having multiple records (these records are distinct). Need urgent help on these 2 points.

If first point gets resolved I can go ahead with that and later will see the calender & fact table relationship.

my skype id is anupam_p1979@hotmail.com. please ping me if need 

Thanks,

Anupam

[v-shex-msft · Accepted solution]

Hi @anupampandey,

You can refer to below steps to calculate with financial year.

1. Add calculate column to calculate the financial year.

FY = if([Date].[MonthNo]<4,[Date].[Year]-1,[Date].[Year])

2. Write the measure to calculate with financial year.

YTDValue =
VAR currDate = Max(RetailerSummary[Transaction Date])
RETURN
CALCULATE (SUM(RetailerSummary[Tran_Amount]),
FILTER(ALL(RetailerSummary),RetailerSummary[Transaction Date]<=currDate
&&
RetailerSummary[FY] = MAX(RetailerSummary[FY))
)

Regards,

Xiaoxin Sheng