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

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