Forum Discussion
Latest Month,QTR and year
I have a table with 3 years Data(2018,2019,2020-January month only) and am writing a dax to get the latest month,QTR and year available in the data . If i select monthly filter i need latest month data .I tried but am not able to get the correct output.
Requirement- Need latest month , Qtr and Year from date data
1.Latest month=(Month-2020 january is the latest month)
2.latest qtr = janauary 2020 and nov,Dec 2019 data
3.latest Year=january 2019 to january 2020
Hi, Anonymous ;
Try it.
Latest month = CALCULATE(SUM('Table'[value]),FILTER('Table',EOMONTH([Date],0)=EOMONTH(MAX('Table'[Date]),0)))latest qtr = CALCULATE(SUM('Table'[value]),FILTER('Table',[Date]>=EOMONTH(MAX('Table'[Date]),-3)+1))latest Year = CALCULATE(SUM('Table'[value]),FILTER('Table',[Date]>=EOMONTH(MAX('Table'[Date]),-12)+1))The final show:
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
davehusMemorable Member
Hi Anonymous , You can achieve this with dates in period if you have a date range in your model. Have you a sanitized version of your report that you could share?
Did I help you today? Please accept my solution and hit the Kudos button.
v-yalanwu-msftCommunity Support
Hi, Anonymous ;
Try it.
Latest month = CALCULATE(SUM('Table'[value]),FILTER('Table',EOMONTH([Date],0)=EOMONTH(MAX('Table'[Date]),0)))latest qtr = CALCULATE(SUM('Table'[value]),FILTER('Table',[Date]>=EOMONTH(MAX('Table'[Date]),-3)+1))latest Year = CALCULATE(SUM('Table'[value]),FILTER('Table',[Date]>=EOMONTH(MAX('Table'[Date]),-12)+1))The final show:
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.