Total Calculations for Year and dynamic Year column

[Anonymous]

I have a date slicer and trying to achieve the below 

1. Total calculations for the previous 3 years. Fiscal year ends on 04/30. If the date selected is 05/01/2018 to 04/30/2019, Need to calculate the Year totals for 2015 , 2016 & 2017 . 

I used CALCULATE([Current Year Invoice],PREVIOUSYEAR('Calendar'[Date],"04/30")) for the last year. How to restrict to fiscal year ending 04/30 for previous years. 

2. How to dynamically change the column header based on the date selected . if  2019 year is selected, report should have these columns 

2016 Total	2017 Total	2018 Total

if 2018 year is selected.  Column header need to be dynamic based on date selection. 

2015 Total	2016 Total	2017 Total

[v-alq-msft]

Hi, @Anonymous 

Based on your description, I created data to reproduce your scenario.

Table:

Calendar(a calculated table):

Calendar = CALENDARAUTO()

New Table(a calculated table):

New Table = 
ADDCOLUMNS(
    DISTINCT('Calendar'[FY]),
    "Num",
    RANKX(
        DISTINCT('Calendar'[FY]),
        [FY],,ASC
    )
)

There is a relationship between 'Calendar' and 'Table'.

You may create a calculated column and two measures as below.

Calculated column:
FY = 
var _date = 'Calendar'[Date]
var _year = YEAR(_date)
var _month = MONTH(_date)
return
IF(
    _month<=4,
    (_year-1)&"-"&_year,
    _year&"-"&(_year+1)
)

FY Total = 
CALCULATE(
    SUM('Table'[Invoice]),
    DATESYTD('Calendar'[Date],"04-30")
)

Visual control = 
var _selectedfy = SELECTEDVALUE('New Table'[FY])
var _selectednum = SELECTEDVALUE('New Table'[Num])
var _fy = SELECTEDVALUE('Calendar'[FY])
var _num = LOOKUPVALUE('New Table'[Num],'New Table'[FY],_fy)
return
IF(
    ISFILTERED('New Table'[FY]),
    IF(
        _selectednum-_num<=3&&
        _selectednum-_num>0,
        1,0
    ),
    1
)

Finally you need to put the measure 'Visual control' in the corresponding visual level filter to display the result.

Best Regards

Allan

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

[Anonymous]

Hi @v-alq-msft 

Thanks for taking the time to respond. That didn't help me on the column header question. 

Attaching a link to the test model. https://drive.google.com/open?id=1yfKw2A5G4rqMtQoy7cdBbrqFfDyGhxju

I wanted to know if it is possible to dynamically get the column headers. 

If the selected slicer is for the fY 2020, I will need to show the prior 2 year totals.   

FY 2018  FY 2019  FY2020  . If the slicer is for FY2019,  the column name should be  FY2017 FY2018 FY2019. 

In the below, instaed of Last year, is it possible to derive column name as 2019? 

[AllisonKennedy]

@Anonymous  Try using DATEADD instead of PREVIOUSYEAR. This allows you to specify which period (YEAR, MONTH or DAY) and how many (so -1 for previous year, -2 for 2 years ago, -3 for 3 years ago).

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 

If you found this post helpful, please give Kudos.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 

https://sites.google.com/site/allisonkennedycv

[Anonymous]

Thanks @AllisonKennedy . 

But, I thought this will take the calendar year rather than the fiscal year that i wanted.Let me test it out. 

Also, the second part of the question is what i was more keen to figure out if it is possible to dynamically change the column header according to the year derived.