Forum Discussion
POSPOS
Post Partisan
Post PartisanShow 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. ...
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'))RETURNIF( 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 👍🤠
POSPOSPost Partisan
Post PartisanDaniel29195 -
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
Community ChampionMeasure =
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.