How to show the count for last 5 years and median for last 8 quarters

[POSPOS]

Hi,

I have a requirement to show the count for last 5 years and median for last 8 quarters  based on submission date.

One column chart with - 5 years of count and median

Second column chart by last 8 Quarters 

There is a page level filter where activation data is not blank.
If there is no data for the current year then , it should should still show 5 years and 8 quarters based on the submission date.
Eg: If there is no data for current year, 1st chart should show data from 2023 to 2019.

Sample pbix available here
can someone please suggest on how to achieve this?

[v-nuoc-msft]

Hi @POSPOS 

Thank you very much S7728 and lbendlin for your prompt reply.

For your question, here is the method I provided:

You need a date table to use as a year in the slicer.

Create a new table. And create a slicer.

Table = 
CALENDAR(
    DATE(YEAR(TODAY()), 1, 1), 
    TODAY()
)

Create measures.

count1 = 
var _year = SELECTEDVALUE('Table'[Date].[Year])
RETURN 
    CALCULATE(
        DISTINCTCOUNT(data[Protocol Number]),
        FILTER(
            'data',
            'data'[Year] >= _year - 5
            &&
            NOT(ISBLANK('data'[Activation Date])
            
        )
    )
)

median1 = 
var _year = SELECTEDVALUE('Table'[Date].[Year])
RETURN
CALCULATE(
    MEDIAN(data[datediff]),
    FILTER(
        'data',
        'data'[Year] >= _year - 5
    )
)

You can view Page 1 and page 2.

Regards,

Nono Chen

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

[S7728]

Count for the Last 5 Years:

CountLast5Years =
CALCULATE(
COUNT('Table'[SubmissionDate]),
FILTER(
'Table',
'Table'[SubmissionDate] >= DATE(YEAR(TODAY()) - 5, 1, 1)
)
)

Median for the Last 8 Quarters:

MedianLast8Quarters =
CALCULATE(
MEDIAN('Table'[SubmissionDate]),
FILTER(
'Table',
'Table'[SubmissionDate] >= EDATE(TODAY(), -24)
)
)

[lbendlin]

Not really clear what you need but here's a proposal.