Showing Data by Financial Year

[Anonymous]

I have data that has open and closed dates in it as well as reasons that something has occured. I also have a calendar table set up with reference date in.

I want to be able to show a graph with the number of times a reason occured in cases that were opened in 17/18, the number they occured in 18/19 and the reasons for current open cases.

I presume I need to do something with measures but I am not sure what

Thanks

[v-cherch-msft · Accepted solution]

Hi @Anonymous 

You may use below measure to get the count for each year.Attached simplified sample file for your reference.

Count =
CALCULATE (
    DISTINCTCOUNT ( Table1[CaseID] ),
    FILTER (
        GENERATE ( Table1, 'Calendar' ),
        Table1[Open date] <= 'Calendar'[Date]
            && Table1[Close date] >= 'Calendar'[Date]
    )
)

CountTotal = SUMX(VALUES('Calendar'[Date].[Year]),[Count])

Regards,

[Anonymous]

Perfect - thank you 🙂