Need to add Fiscal Years columns to a matrix report

[aghanchi]

Hi,

Our non-profit needs to report numbers based on fiscal years (instead of calendar years), that starts in October and ends in September next calendar year. We do case management for people with disabilities. There is an Open Date and a Close date for each case. A case can remain open across multiple fiscal years. This can tell you that I cannot just add a custom column based on a formula that calculates a single fiscal year. Because, as I mentioned, a case can span across multiple fiscal years. I need to create a matrix report in PowerBI that shows the number of clients served under each fiscal year categorized by disability (see example table at the end). What should I be doing at the table Transformation, or on the report filters that would give me these results? I will appreciate any help on this. Thanks.

Disability	Individuals Served in Fiscal Year 2019	Individuals served in Fiscal Year 2020
Blind	20	32
Deaf	15	21
Mental Illness	10	15

[v-alq-msft · Accepted solution]

Hi, @aghanchi 

Based on your description, I created data to reproduce your scenario, The pbix file is attached in the end.

Calendar(a calculated table):

Calendar = CALENDARAUTO()

You may create a measure as below.

Result = 
var fy = SELECTEDVALUE('Calendar'[Fiscal Year])
var tab = 
ADDCOLUMNS(
    'Table',
    "Flag",
    var enddate = 
    IF(
        ISBLANK([Close Date]),
        TODAY(),
        [Close Date]
    )
    var t = 
    SELECTCOLUMNS(
        ADDCOLUMNS(
            CALENDAR(
                [Open Date],
                enddate
            ),
            "FY",
            IF(
                MONTH([Date])>=10,
                "Fiscal Year "&YEAR([Date]),
                "Fiscal Year "&YEAR([Date])-1
            )
        ),
        "FY",[FY]
    )
    return
    IF(
        fy in t,
        1,0
    )
)
return
COALESCE(
    COUNTROWS(
        FILTER(
            tab,
            [Flag]=1
        )
    ),0
)

Finally you may create a matrix with 'Fiscal Year' from 'Calendar' 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.

[aghanchi]

Hi Allan.

Thank you very much! Your solution is spot on! I was able to make it work with little correction as follows. Otherwise, it worked perfectly and matches the result of my SQL query:

"Fiscal Year "&YEAR([Date])+1,
"Fiscal Year "&YEAR([Date])

I am relatively new to PBI. @amitchandak's resources were also very helpful in making me understand the basics. He has a youtube channel also.

It was frustrating for me because I was not able to name the problem. Now I have a name for it: "Events-In-Progress", with a little twist of Fiscal/Financial years. That's right. Thanks to this article by Gerhard. I was giving serious consideration to his solutions but somehow was not able to replicate my query's results. I will see if I can make it work and post it as an alternate solutuon. 

For now, your solution is the best. Thanks so much for responding!

[amitchandak]

@aghanchi , for Oct-Sep calendar you can refer

Creating Financial Calendar - From Any Month
https://community.powerbi.com/t5/Community-Blog/Creating-Financial-Calendar-Decoding-Date-and-Calend...

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

To distribute or check one of three approaches

https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-o...

File attached after signature

Or the HR Blog -https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[aghanchi]

Hi Amit, I am sorry it doesn't look like that the solutions you have suggested, address my situation. They are good if we want to use a single date column to determine a financial year, quarter, week etc. In my case, I have a range of Open and Close dates for each case. This date range can span across multiple fiscal years and hence that case will be counted under those respective columns of the fiscal years.

I am providing the sample PIBX and source Excel (download link at the end). The second tab (Sheet 3) of the Excel is the final report I want to see in Power BI. I created it using a complicated SQL Query. I hope I can do the same in PBI, but more efficiently. In the source data, I have identified few cases that would span through multiple fiscal years. There may be more. Such cases will be counted under each fiscal year column as seen in the final pivot table. I hope I was able to explain well. Otherwise, please don't hesitate to ask for explanation.

Thank you.

https://www.dropbox.com/sh/r97b9igio4qyvgf/AAAhMUWarNmaEOaK2Guew1Fja?dl=0