Fiscal Year Calculation`

[ak77]

Hi All,

Thanks much  for helping out till date !..

I need help on the below requirement where we have to display fiscal year sum of returns for multiple clients in a measure.. til now i had clients which had year end date 31/03 and i was using below measure 

FYTD =CALCULATE([Total ReturnV1],DATESYTD('Date Table'[_Date],"31/03"))

Now i am getting the year end date in a separate table as below and user expects to use the year end date from the table in the measure.  i tried replacing the hardcoded value (31/03) with the column YearEnd .. but its not allowing me to do so and the option of selecting this column is not available to use..  Can anyone please help on this 

[Ahmedx]

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

https://1drv.ms/u/s!AiUZ0Ws7G26RikbVQdUS5C2JC-Ho?e=xqio2U

[Ahmedx · Accepted solution]

you can also use this measure to get the same data

Sales YTD = 
VAR __SUM = CALCULATE (
    [Amo],
    VAR FirstFiscalMonth = [StartFY] -- Set the first month of the fiscal year
    VAR LastDay =
        MAX ( 'Calendar'[Date] )
    VAR LastMonth =
        MONTH ( LastDay )
    VAR LastYear =
        YEAR ( LastDay )
            - IF ( LastMonth < FirstFiscalMonth, 1 )
    VAR FilterYtd =
        DATESBETWEEN (
            'Calendar'[Date],
            DATE ( LastYear, FirstFiscalMonth, 1 ),
            LastDay 
        )
      
    RETURN
   FilterYtd)
   VAR _MaxdareSales = MAX('Sales'[Business Days])
   RETURN
   IF(_MaxdareSales,__SUM)

[Ritaf1983]

Hi @ak77 
You can use some conditions like :
If (month(max('yourtable[yearend]))= 3,
FYTD =CALCULATE([Total ReturnV1],DATESYTD('Date Table'[_Date],"31/03")),
FYTD =CALCULATE([Total ReturnV1],DATESYTD('Date Table'[_Date],"31/12"))
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[ak77]

@Ritaf1983 , thanks for reply.. can i use FYTD =CALCULATE([Total ReturnV1],DATESYTD('Date Table'[_Date],yourtable[yearend]) the year field as the parameter to the function? 

[Ritaf1983]

Hi @ak77 
The suggested way is not correct for sure because 'yourtable[yearend]' is a column and not a scalar value, so you can't filter by it ( because the column is a multiple values and not one).
You can try to modify it to :
FYTD =CALCULATE([Total ReturnV1],DATESYTD('Date Table'[_Date],max(yourtable[yearend])
just try, there is no way a computer can defeat you 🙂

[ak77]

No Luck ! Sorry 

[Ritaf1983]

What is wrong with my first suggestion?
For a more detailed solution please share some dummy PBIX to work with.

[ak77]

The user does not want the date to be hardcoded FYTD =CALCULATE([Total ReturnV1],DATESYTD('Date Table'[_Date],"31/03")),

He wants it to be taken from a column name  coming from DB Table..  so if these value changes .. they can just update it in the DB column without any report changes