Fiscal Last Year to date

[betterreportsDK]

Hi

I have an issue creating a measure which offsets the fiscal year by 1, but I have yet to find a solution. 

I have a datetable with fiscal year, fiscalyearnumeriic, fiscalyearoffset etc.

I have managed to create a Fiscal Year To date measure using this: 

Rev YTD = VAR vMaxDate=MAX(calender_tbl[Date])

RETURN

CALCULATE(sum(Sales[Rev]),

ALL(calender_tbl),

VALUES(calender_tbl[FiscalYear]),

calender_tbl[Date]<=vMaxDate

)

[betterreportsDK · Accepted solution]

I managed to find a solution to this problem, using offset function. I will adjust it to take leap year inte account when I get the time. It works, but I might have overlooked something. 

LFYTD=last fiscal year to date

Rev FYTD=revenue fiscal year to date measure presented above

Rev LFYTD =

CALCULATE([Rev FYTD],

OFFSET(

    -365,

    ALLSELECTED(calender_tbl[Date]),ORDERBY(calender_tbl[date])))

[Rupak_bi]

Hi, If you still seaching for the solution, please share sample data representing your dataset. all these solutions will work depending upon how you are deploying. also share the desired output based on the sample data.

[xifeng_L]

Can try below measure:

Rev PY YTD = 
CALCULATE(
    SUM(Sales[Rev]),
    DATEADD(DATESYTD(Calendar_Tbl[Date]),-1,YEAR)
)

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

Thank you~

[betterreportsDK]

Hi. xifeng_L. Thank you for your reply. Where do you take fiscal year into account - as I see it it will be based on the regular year?

[dharmendars007]

Hello @betterreportsDK 

Assuming you have a field like FiscalYearOffset in your calendar_tbl (which indicates an offset value), here’s how you can tweak your measure.

Rev YTD =
VAR vMaxDate = MAX(calendar_tbl[Date])
VAR vFiscalYearOffset = -1 -- Offset Fiscal Year by 1 (You can adjust this)

RETURN
CALCULATE(SUM(Sales[Rev]),ALL(calendar_tbl),
VALUES(calendar_tbl[FiscalYear] + vFiscalYearOffset), -- Apply Offset
calendar_tbl[Date] <= vMaxDate)

This formula assumes that you want to offset the fiscal year by subtracting one year. Adjust the vFiscalYearOffset as needed (e.g., use +1 if the logic requires advancing the fiscal year)

If you find this helpful , please mark it as solution which will be helpful for others and Your Kudos/Likes 👍 are much appreciated!

Thank You

Dharmendar S

LinkedIN 

[betterreportsDK]

Hi Dharmendar S

Thank you for your suggestion. I get the error that "a single value for calendar_tbl[FiscalYear] cannot be be determined". I have defined FiscalYear is a whole number, which I think makes sense.