Show last 8 Quarters using DAX

[POSPOS]

Hi All,

I have a requirement to show only the last 8 quarters in the report.

If there is no data in a particular quarer then show as zero. Attached below is a sample chart and pbix here.

Can someone pls suggest on how to achieve this using DAX.

Thank you

[Daniel29195 · Accepted solution]

@POSPOS 

output : 

measure : 

Measure =

var m = CALCULATE(MAX('154'[Date]), ALL('154'))

var datasource =

CALCULATETABLE(

    all('154'[Date]),

    '154'[Date] >= EDATE(m,-8 * 3 ),

    ALL('154')

)

RETURN

 IF( MIN('154'[Date])>=EDATE(m,-8 * 3 ) , COUNT('154'[Seq No]),blank())

If my response has successfully addressed your issue kindly consider marking it as the accepted solution! This will help others find it quickly. I would appreciate hitting that kudos button 👍🤠

[POSPOS]

@Daniel29195  - 
After testing this with more data, I noticed that the measure is showing only last 6 quarters instead of 8.
I have attached a sample report here.
Could you please advise why I am getting only 6 quarters instead of 8.
Thank you.

[Daniel29195]

@POSPOS 

Measure =

var m = CALCULATE(MAX('data8Q (2)'[Submit Date]), ALL('data8Q (2)'))

var datasource =

CALCULATETABLE(

    values('data8Q (2)'[Submit Date]),

    'data8Q (2)'[Submit Date] >= EDATE(m,-8 * 3 ),

    ALL('data8Q (2)')

)

RETURN

     CALCULATE(COUNT('data8Q (2)'[SerNo]) , datasource)

it will show 7 quarters.

if you remove the filter on activation is not blank() , it will show  8

 

[POSPOS]

Thanks @Daniel29195  - Our requirement is to apply activation complete filter to exclude all the blanks dates.

[Daniel29195]

@POSPOS 
simply add the filter slicer, 
but then ,

the first quarter in the chart will disappear since in this quarter the activation complete is blank () 

so it will show only 7 quarters.