Forum Discussion

jpalm's avatar
jpalm
Regular Visitor
5 years ago
Solved

Fiscal Year Comparison PY

I am very new to Power BI and am having some trouble. I have researched this issue and tried to use multiple solutions that would seem to have solved the original poster's problem but are somehow lost in translation when I try to apply it to my issue. I have a date table with the following columns added: 

 

Financial Year =
SWITCH (
TRUE (),
'Date Table'[MonthNo] >= 7
&& 'Date Table'[MonthNo] <= 12, 'Date Table'[Year] & "-FY",
'Date Table'[MonthNo] >= 1
&& 'Date Table'[MonthNo] <= 6, 'Date Table'[Year] - 1
& "-FY"
)

 

FY Month Sort =
SWITCH (
TRUE (),
'Date Table'[MonthNo] = 1, 7,
'Date Table'[MonthNo] = 2, 8,
'Date Table'[MonthNo] = 3, 9,
'Date Table'[MonthNo] = 4, 10,
'Date Table'[MonthNo] = 5, 11,
'Date Table'[MonthNo] = 6, 12,
'Date Table'[MonthNo] = 7, 1,
'Date Table'[MonthNo] = 8, 2,
'Date Table'[MonthNo] = 9, 3,
'Date Table'[MonthNo] = 10, 4,
'Date Table'[MonthNo] = 11, 5,
'Date Table'[MonthNo] = 12, 6
)
 
FY Month =
'Date Table'[MonthShort]
 
And one of the measures I am using: 
Billable =
CALCULATE(SUM('WIP'[Hours]), 'WIP'[IsBillableFlag] IN { TRUE })
 
and the prior year measure: 
 
Billable LY =
CALCULATE (
[Billable],
CALCULATETABLE ( SAMEPERIODLASTYEAR ( 'Date Table'[Date] ))
)
 
FY MonthFY Month SortBillableBillable LY
Jul1XXXXXXXX
Aug2XXXXXXXX
Sep3XXXXXXXX
Oct4XXXXXXXX
Nov5XXXXXXXX
Dec6XXXXXXXX
Jan7 XXXX
Feb8 XXXX
Mar9 XXXX
Apr10 XXXX
May11 XXXX
Jun12 XXXX

My billable LY date continues on past my current year end. The fiscal year ends June 30th so my current year is properly showing July through December but my last year column just continues on past december - I would like it to stop at whatever the current year month is for comparion's sake.

 

Thank you.

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi jpalm 

    I think your may want to show values without current year's by your measure.

    Ex. Current year is 2020, you show values that Year =2020 and FY = 2020.

         Last year is 2019, you want to show values that Year = 2019,and FY =2019 (Jun to Dec)

    I think you can add a filter in your Billable LY  measure.

    Due to I don't know your data model, I build a sample to have a test.

    Date Table = 
ADDCOLUMNS (
    CALENDAR ( DATE ( 2019, 01, 01 ), DATE ( 2020, 12, 31 ) ),
    "Year", YEAR ( [Date] ),
    "MonthNo", MONTH ( [Date] ),
    "MonthShort", FORMAT ( [Date], "mmm" )
)

    Add Financial Year, FY Month, FY MonthNo like yours

    Sample Data:

    I don't know your logic about Flag column ,I guess it may show true if date Year and date month = today 's year and month.

    Try this measure:

    Billable LY =
CALCULATE (
    SUM ( 'WIP'[Hours] ),
    FILTER (
        WIP,
        WIP[Year]
            = YEAR ( TODAY () ) - 1
            && WIP[FY Year]
                = YEAR ( TODAY () ) - 1
    )
)

    Result is as below.

    You can download the pbix file from this link: Fiscal Year Comparison PY

     

    Best Regards,

    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    jpalm , In case you need YTD , try with time intelligence and date table

     

    YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"6/30"))
    Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"6/30"))

     

    This month ve last year same month

    MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))

    last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))
    last year MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))

     

    or

     

    Month behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Month))
    Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

     

    To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

    • jpalm's avatar
      jpalm
      Regular Visitor
      5 years ago

      Thank you-my measures are pulling the right amounts as compared to the prior year but for instance January is showing on the LY column even though we have not yet got to January (for my fiscal year, which runs from July to June). If that makes sense. How can I stop the PY from showing for the entire fiscal year?

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Hi jpalm 

        I think your may want to show values without current year's by your measure.

        Ex. Current year is 2020, you show values that Year =2020 and FY = 2020.

             Last year is 2019, you want to show values that Year = 2019,and FY =2019 (Jun to Dec)

        I think you can add a filter in your Billable LY  measure.

        Due to I don't know your data model, I build a sample to have a test.

        Date Table = 
ADDCOLUMNS (
    CALENDAR ( DATE ( 2019, 01, 01 ), DATE ( 2020, 12, 31 ) ),
    "Year", YEAR ( [Date] ),
    "MonthNo", MONTH ( [Date] ),
    "MonthShort", FORMAT ( [Date], "mmm" )
)

        Add Financial Year, FY Month, FY MonthNo like yours

        Sample Data:

        I don't know your logic about Flag column ,I guess it may show true if date Year and date month = today 's year and month.

        Try this measure:

        Billable LY =
CALCULATE (
    SUM ( 'WIP'[Hours] ),
    FILTER (
        WIP,
        WIP[Year]
            = YEAR ( TODAY () ) - 1
            && WIP[FY Year]
                = YEAR ( TODAY () ) - 1
    )
)

        Result is as below.

        You can download the pbix file from this link: Fiscal Year Comparison PY

         

        Best Regards,

        Rico Zhou

         

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.