topic Re: Fiscal Last Year to date in DAX Commands and Tips

Fiscal Last Year to date

betterreportsDK — Mon, 21 Oct 2024 12:13:00 GMT

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

)

Re: Fiscal Last Year to date

dharmendars007 — Mon, 21 Oct 2024 13:12:32 GMT

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

Re: Fiscal Last Year to date

xifeng_L — Mon, 21 Oct 2024 14:55:15 GMT

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~

Re: Fiscal Last Year to date

betterreportsDK — Mon, 21 Oct 2024 16:22:02 GMT

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.

Re: Fiscal Last Year to date

betterreportsDK — Mon, 21 Oct 2024 16:26:36 GMT

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?

Re: Fiscal Last Year to date

Rupak_bi — Tue, 22 Oct 2024 08:14:57 GMT

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.

Re: Fiscal Last Year to date

betterreportsDK — Wed, 23 Oct 2024 11:09:04 GMT

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])))