Forum Discussion

Icon for Post Partisan rank

Post Partisan

2 years ago

Solved

Show last 8 Quarters using DAX

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
2 years ago
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 👍🤠

5 Replies

Daniel29195
Community Champion
2 years ago
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
  Post Partisan
  2 years ago
  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
    Community Champion
    2 years ago
    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