Last YTD DAX calculation by Fiscal Year

[PradipMCT]

Hi All,

Few time intelligence functions like, TotalYTD(), Parallelperiod(), Sameperiodlastyear() are few functions that work great with the Calendar year but how to use these functions with fiscal years?

[pedrodax23]

Hi @v-sihou-msft 

I am trying to solve this problem of comparing to TOTAL Last Financial Year to date against monthly Financial Year to Date.

Se image below to understand better what I need

I saw your suggestion and did not understand but I kind of found a workable solution. Although I do not understand much of what is going on and also think it is not so elegant solution.

here is the working code.

PreviousFY_Working = 
CALCULATE (
    CALCULATE (
        [SalesAmount],
        DATESBETWEEN (
            'Date'[Date],
            DATE ( YEAR ( MAX ( 'Date'[Date] ) ), 7, 1 ),
            DATE ( YEAR ( MAX ( 'Date'[Date] ) ) + 1, 6, 30 )
        )
    ),
    DATEADD ( 'Date'[Date], -18, MONTH )

        )

[hemantsingh]

Hi @PradipMCT,

   You can use the function "DATESYTD" for this purpose.

This link will help you in understanding it better. 

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/0ff756db-5ecc-4e66-bb26-3e03bec7c142/fiscal...

Regards,

Hemant

[PradipMCT]

Hey Hemant

Thanks. Ibam aware of this function. However i was looking the alternative to parallelperiod () and sameperiodlastyear () functions. The totalytd ()is the alternative to datesytd ()

[v-sihou-msft · Accepted solution]

@PradipMCT

For TOTALYTD(), you can add an argument to specify the "end-of-year" for Fiscal scenario. For SAMEPERIODLASTYEAR() and PARALLELPERIOD() function, you can only use DATEADD() function on date column to achieve time intelligence.

Regards,

[hemantsingh]

U trying to achieve running totals??? It would be great if you can provide some sample data & the end result you want to achieve.

Regards,