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 c...
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)
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 🙂

  • ak77's avatar
    ak77
    Post Patron
    2 years ago

    No Luck ! Sorry 

    • Ritaf1983's avatar
      Ritaf1983
      Super User
      2 years ago

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

      • ak77's avatar
        ak77
        Post Patron
        2 years ago

        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