Forum Discussion

ak77's avatar
ak77
Post Patron
2 years ago
Solved

Fiscal Year Calculation`

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 
 

 

 
Need Help
Show and Tell
  • Ahmedx's avatar
    Ahmedx
    2 years ago

    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)

8 Replies

  • Ritaf1983's avatar
    Ritaf1983
    Super User
    2 years ago

    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's avatar
      ak77
      Post Patron
      2 years ago

      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's avatar
        Ritaf1983
        Super User
        2 years ago

        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 🙂

    • Ahmedx's avatar
      Ahmedx
      Super User
      2 years ago

      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)