Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Showing Data by Financial Year

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

Need Help
  • v-cherch-msft's avatar
    v-cherch-msft
    7 years ago

    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,

2 Replies

  • v-cherch-msft's avatar
    v-cherch-msft
    Microsoft Employee
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      Perfect - thank you :-)