Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
aghanchi
Frequent Visitor

Need to add Fiscal Years columns to a matrix report

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.

 

 

DisabilityIndividuals Served in Fiscal Year 2019Individuals served in Fiscal Year 2020
Blind2032
Deaf1521
Mental Illness1015
1 ACCEPTED SOLUTION
v-alq-msft
Community Support
Community Support

Hi, @aghanchi 

 

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

f1.png

 

Calendar(a calculated table):

Calendar = CALENDARAUTO()

f2.png

 

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.

f3.png

 

Best Regards

Allan

 

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

 

View solution in original post

4 REPLIES 4
v-alq-msft
Community Support
Community Support

Hi, @aghanchi 

 

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

f1.png

 

Calendar(a calculated table):

Calendar = CALENDARAUTO()

f2.png

 

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.

f3.png

 

Best Regards

Allan

 

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

 

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
Super User
Super User

@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...

 

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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.